Merge 2 Excels while order of column names are not same in the files


I need to merge two excel files. each file has same column names but their orders are not samefile1.xlsx (14.5 KB)

File2.xlsx (14.5 KB)
FileMerge.xlsx (14.3 KB)

simply read range for each file and then append to merged file (or merge datatable) is not working as expected.

Can anyone advise?
I am still learning.


1 Like

Try this workflow, select all your files accordingly, should work!
Mergesheet.xaml (9.6 KB)

1 Like

In both the files order of column names are not same.
say for e.g.,
file1 has “Name” in Col1, “address” in col2, ‘Age’ in col3, ‘ID’ in Col4.
file2 has “address” in Col1, “Name” in Col2 etc and so on.
Also merged file should have “ID” in Col1 and “Name” in Col2, ‘Age’ in Col3, address in col4 .
sample files i have attached in my earlier post.
I was able to merge them but order of column names are different.
So read and then Merge table is not working as expected.

Please help.

Order doesn’t matter, it will merge as per destination table, i already tried on your sample files and it is working.

Yes, your workflow is perfectly working.
actually, I need to merge N number of files and then merge them into one file and merge data table was not working for me. So I thought we can not use read range and then merge data table when column orders are different.

Your prompt reply with the solution is much appreciated! :slight_smile:

Merging N number of file is doable, just need to use do-while or for loop, you can keep all files in one folder and the customize the file read operation by giving filename as a parameter :smile:

I hope this solves your query!