I'm trying to combine 12 worksheets by adding them all to a directory > using a formula to add the sheet name of each file (they are all the same) > Use a dynamic input to combine all the data
The issue I'm having is all of the worksheets have the same headings in the same order, except 1 sheet has 2 columns in a different position like so:
Date | Customer | City | State | ZipCode |
Date | Customer | City | State | ZipCode |
Date | Customer | City | ZipCode | State |
Date | Customer | City | State | ZipCode |
Date | Customer | City | State | ZipCode |
So I am getting ZipCode combined with state and vice versa:
Date | Customer | City | State | ZipCode |
2/9/17 | ACME | Newark | NJ | 07982 |
2/9/17 | Smith CO | Camden | 02849 | NJ |
2/9/17 | Engineering Inc | Harrison | NJ | 09837 |
2/9/17 | Mechanical LLC | Philadelphia | 89307 | PA |
How can I make sure that all fields are being combined together?
I could very easily open the spreadsheet and cut/paste the columns but since this will be a constant flow of data I don't want to have to do that every time we have new data. It would also be useful to know how to do this in the future
Solved! Go to Solution.
Hi @abovino
I'd recommend converting the dynamic input part into a batch macro. When in the macro you can open the interface designer (ctrl + alt + d, or View -> Interface Designer).
The final tab has an output mode option which you can set to 'auto configure by Name (wait until all iterations run). This will mean that the column headers get matched up and your data will be correctly ordered.
I've attached the macro as well.