Combine Excel sheets with different column order

[Mario36]

I need to merge an excel file with same data on all tabs but the column order is different. I did try dynamic input tool but it merged based on position.

[alexvornsand]

Hi @Mario36, it's funny that the dynamic input tool doesn't have an option for configuring based on name. Maybe this is something you could submit as a suggestion? I was able to solve your issue by outsourcing the Union portion of the tool to a macro. See the attached workflow. Basically, the macro does the work of bringing in each sheet, but then the macro output is stacking the data (specifically by name).

[gabrielvilella]

There is an idea for this topic, if more people could upvote it Alteryx may consider it. 

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Read-in-multiple-files-tabs-with-different-f... 

[mceleavey]

Hi @Mario36 ,

I built a couple of tools to do this.

The first reads the Excel tab names, these are then added to the fullpath and the second tool loads them all in and merges them according to the column names.

I hope this helps,

M.

[JosephSerpis]

The Batch Macro approach highlighted in this article should help as you can merge by name and postion.

[Mario36]

Thank you everyone for your solutions. What if one or more columns have the same data but the column names are a bit different from the rest.

Sheet 04 on the attached excel is the one I wanted to be merged.

@alexvornsand Your solution wasn't able to append all the data. I believe they appended only the common items.

@mceleavey Your solution helped. Could you assist in resolving the above block ?

[mceleavey]

Hi @Mario36 ,

If your column names are different then you will need to rename them. There's no way for Alteryx to know they're the same.

If your columns are named consistently different then you can use a mapping input and the Dynamic rename tool to rename.

However, you will need to split them on filename rename, then union.

This is a stark example of Excel being a really bad data source.

M.

[Mario36]

So nothing can be done by grouping them by data type or size ?

[mceleavey]

Hi @Mario36 ,

can you explain what the problem actually is? 

The example you gave loads perfectly through the tool I provided. Can you give an example of a problem you're trying to resolve?

M.

[Mario36]

This is what I want to achieve

Product	Cost	Quantity	Date
Apple	100	10	2/7/2022
Mango	120	12	2/7/2022
Apple	100	10	2/5/2022
Orange	150	8	2/6/2022
Banana	95	18	2/1/2022
Grape	85	9	2/3/2022
Grape	65	12	2/10/2022
Plum	80	5	2/6/2022
Guava	150	2	2/10/2022
Papaya	100	5	2/6/2022
Melon	60	12	2/4/2022
Guava	150	2	2/10/2022
Papaya	100	5	2/6/2022
Melon	60	12	2/4/2022