Split the Excel Summary Report based on Headers into separate sheets

Hi All,
I have a excel summary report which has different tables under different headings. I need to split the tables under each heading into separate data sheet based on headings and name each tab with the heading.

Attached is my excel file, it has summary tab from which I need to fetch data and then split them into multiple pages based on headings and name each tab with the heading as shown in the sample file.

output.xlsx (12.3 KB)

Any help or xaml will definitely keep me going forwardwould be appreciated.

@Zahid1 Will the Header names be the same always or does it change? Also Will the First 4 Lines in the Summary Excel Sheet be the same always?

@supermanPunch : Yes the Header names will always remain as same. Since the table is populating based on Header. The first 4 lines will also be the same way. Actually there are 8 lines in top section. Attaching the updated sheet.

output.xlsx (13.4 KB)

Hi Arpan,
It will always remain the same.

@Zahid1 Can you Check this Workflow:

Considering the Header Names will be the same throughout, I have used Regex to find the position of the rows where the Headers are present. In this order I am able to take the rows between Header1 and Header2, Header2 and Header3 and Header3 to the end of rows using Linq operations Take and Skip.

Check if this is the Output that you expected.
SplitDatatableAndCheck.zip (9.8 KB)

Check the output excel file. It contains only Summary Sheet. Execute the Workflow. It Should contain the Sheets with the header names as well. But this is not a very good approach as it is not dynamic i.e if the number of Tables to be separated increases we will have to go for a different approach. Since the tables were three and the Headers are constant this should work.

Let us know if this doesn’t work.

@supermanPunch : Thanks a lot for the xaml, I will update you once I have done my testing.

1 Like

Hi @supermanPunch , I tired your xaml file but for some reason not getting correct output.
Attached is my actual file and the headers are in background yellow.

Summary.xlsx (748.8 KB)

Kindly see if you could help me out.

@Zahid1 The format that you had provided was different, hence it isn’t working :sweat_smile:, I would have to modify and check if it works for this file. But again I’m asking is this Format the same ? Will the Headers be the same always? there aren’t any other files with different headers that you want to Extract right ?

@supermanPunch: Yes the format will be same and this is the file I am working on.

@Zahid1 Do you want all tables under all Headers or only the first three Tables under Three Headers ?

@supermanPunch: All the tables under their respective headers.

@Zahid1 Check this Workflow:
I had to use Excel Application Scope in order to get the Output needed, as the Workbook activities Read Range resulted in an Error while using it.

Also I had to use one extra Package to make the Workflow work. You should install this Package before executing this workflow as it will throw a Missing Activity Error otherwise.

Check the Summary File, You’ll notice Only one Sheet present. Close the Excel. Execute the workflow.
The Sheets will be created with the header names
SplitDatatableAndCheck.zip (516.3 KB)

1 Like

@supermanPunch : Thank you so much. I worked really great. :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.