How to read excel with multiple header and write Excel data in other tab

Hello Folks,

I have an requirement where i have to read multiple header and then transpose the sum.

Requirement:- Excel tab with Multiple header, i have to extract the figure.
i.e. HR = 45000
Finance = 22323
Team4 = 16000(8000+8000)

Expected Result
After need to write that data in there Columns

And the code should be dynamic so that if any other team added it should print accordingly.
Any Suggestion Guys? Attache the sample ExcelMIS Sheet.xlsx (21.0 KB)

@Bala @pavanh003 @samir @balupad14

Hi @Bhupesh_Gupta

Have a look to the below thread. That might give some idea for the same.

Mark as solution and like it if this helps you :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

@Pratik_Wavhal in my condition i have to read data from different headers of same name if you can check image 1


Hi Mate,

Hope you are doing fine!

You can read the entire Sheet as (dtInput)

Then you can use the Headnames (ie: Name, Amount, Teams, etc) , to identify the identify the new table’s excel counters. (Could be tedious but very much possible)

Once you get all the different tables in hand you can loop through each table contents to identify the TEAM (ie: HR, Finance, etc), and fetch the Amounts for each TEAM and keep count for each Team.

And use that info to populate your fields…

Thanks and Regards,


@hacky thank you, if you share any example or xaml file, will be more helpful