Excel:DIfficulty in extracting the data in Excel which has merged headers

Help Activities
, , ,
1

Hi Friends,

I have an Excel which has two header rows, the first header is merged, under that in the next row there are few headers. I want to extract the data considering the first header and then retrieve the values from the below header. Attached the file for reference. Can someone please suggest a solution.

Sampletest.xlsx (11.6 KB)

Thanks
Renuka

2

Can we see the workflow screenshot

3

I could not implement my idea, I was thinking to read the header in the second row and then based on that read the first row header and rename the headers in second row , basically making it as unique headers since there are headers in the second row with the same name.

4

then you can use in the range start with A2 like that means it will read from the second I don’t know its working just try it once

5

I did the same way you suggested but since it has same header names in the second row, I cannot read the range ,it says duplicate column is available. If you check the file I shared, you will get the point.

6

Hi, maybe this could help:

  1. Use Read range of the entire worksheet with the property Add Headers checked. You will get adatatable like this:

image

  1. Use a for each activity, for iterate the columns.

image
image692Ă—489 12.3 KB

Basically the logic is that if you find something different to “ColumnX” (which is the default header that read datatable gives when nothing is in the cell) you need to set that column name to the previous non default header. As you know we cannot have headers with the same name, so here is where that intCounter variable goes in. You will have a datatable as output like this:

image
image756Ă—41 1.99 KB

7

Could you please share workflow i could not understand

8

Thanks for the suggestion, could you please share the workflow you have, I want to know the value you have provided for assign activity, if you don’t mind.

1 Like
9

Yes, sure.

MergeHeaders.xaml (11.4 KB)

1 Like
10

Thanks a lot for sharing the workflow, i Shall implement and get back with the outcome.