How Do I Combine Multiple One-Column XLSX Files Into One File With Multiple Columns?

I have 200 .XLSX files that are numbered 1 - 200, each of them containing one column of data (with an equal number of rows).

I want to combine all of these files into one file that has 200 columns of data, and I need the columns to be imported into the single file in the same order as the order of the .XLSX files.

Is there any way to accomplish this?

Because I was bored I wrote a macro that

A) assumes the file names goes from 1.xlsx, 2.xlsx, ............. 200.xlsx

B) there is only one sheet in all these 200 files

You need to specify the folder where the files are found i.e. you must change the sPath "=C:\Temp\" to this folder, don't forget the last "\" if missing from sPath macro will not run.

Result will be written to firs empty column in target file (the file that contains this macro) In the first row you will get the file name i.e. "1.xlsx" in cell A1 and all the data from this file in A2 and downwards. "2.xlsx" in B1 and so fort. If a file is missing i.e. "125.xlsx" but not "126.xlsx" macro will ignore missing file and move on to next file.

At the moment loop is set for 200 files. If there are other files in this folder these will be ignored only files with names from "1.xlsx" to "200.xlsx" will be processed.

Please Login or Register  to view this content.

Alf

Originally Posted by Pepe Le Mokko

This addin should do the trick http://www.rondebruin.nl/win/addins/rdbmerge.htm

Read the notes carefully. They explain how to put columns next to each other

This worked like a charm, but I had to start the numbering at 100 to get it to read the files in the correct order. Thanks!

Also, thank you to Alf for the code, I hope someone out there will be able to put it to use.

I hope someone out there will be able to put it to use

No problem, I enjoyed myself figuring out this code.

Alf