Combining several excel worksheets but 1 worksheet has 2 columns in a different position

Question

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:

DateCustomerCityStateZipCodeDateCustomerCityStateZipCodeDateCustomerCityZipCodeStateDateCustomerCityStateZipCodeDateCustomerCityStateZipCode

So I am getting ZipCode combined with state and vice versa:

DateCustomerCityStateZipCode2/9/17ACMENewarkNJ079822/9/17Smith COCamden02849NJ2/9/17Engineering IncHarrisonNJ098372/9/17Mechanical LLCPhiladelphia89307PA

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

DataBlender · Accepted Answer

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.