Read data multiple tabs with different headings

[tandon]

I have excel file with multiple tabs with different headings and different order. I am able to design the batch macro / dynamic input which corrected the order however for different headings, need to handle it. please comment.

Below are example where I have data in different tabs. I would like to import all in two columns i.e. Date and Sales. 

[DataNath]

Hey @tandon, will the data column always have 'date' somewhere in the header? If so you could use a straightforward dynamic rename formula like so:

Before:

After:

Can also use the following which looks for the '£' symbol to indicate sales:

Just shout if not. Thanks!

[IraWatt]

Hey @tandon,

Would be good to have your workflow but what I would do is this:

As each file comes in your macro transpose it then use find and replace to sort the columns to a standardised output so they can all union by name.

Any questions or issues please ask :)
HTH!
Ira

[tandon]

Thanks @DataNath. there are more then 50 tabs in my excel and all have different abbreviations for date and Amount. I tried to use above approach but then I need to code for all different abbreviations for date and amount. I am trying to design something dynamic like and avoid coding formulas for 50 different tabs.

[tandon]

Thanks @IraWatt . forgot to mention - I have excel file with more then 50 tabs. I am unable to upload workflow due to restriction. 

[DataNath]

Thanks for clarifying @tandon. Edited my original post but I think the transpose option I added since will help! This will bring all the headers into columns and then conduct the checks from there.

[binuacs]

@tandon one way of doing this with the Union tool with the property AutoConfig by position

[tandon]

Thanks @binuacs . Union may not work as I have data in single excel with multiple tabs

[tandon]

Thanks @DataNath . I  am expecting output as below . I will explore transpose approach however it seems to be expensive (in terms of coding).

[IraWatt]

@tandon The find and replace method just requires a lookup table if you dont want to code.