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

[TheDudeDude]

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?

[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

[TheDudeDude]

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.

[Alf]

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.

Option Explicit

Sub ProcessFiles()

    Dim sPath As String
    Dim Wb As Workbook
    Dim sFile As String
    Dim i As Integer
    Dim k As Integer
    
    
    sPath = "C:\Temp\"
  
    Application.ScreenUpdating = False

    Do While i <= 200
    
    i = 1 + i
    
    sFile = Dir(sPath & i & ".xlsx")
    
    If sFile = "" Then GoTo skipper
    
        Set Wb = Workbooks.Open(sPath & sFile)
         ActiveSheet.UsedRange.Copy
        Wb.Close SaveChanges:=False
     
    If Range("A1") = "" Then
      Range("A1").Value = sFile
      Range("A2").PasteSpecial xlPasteAll
   Else
      k = Cells(1, Columns.Count).End(xlToLeft).Column + 1
      Cells(1, k).Value = sFile
      Cells(2, k).PasteSpecial xlPasteAll
   End If
   
    sFile = Dir
    
skipper:
        
    Loop

Application.CutCopyMode = False

   Application.ScreenUpdating = True
    
End Sub

Alf

[Alf]

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

No problem, I enjoyed myself figuring out this code.

Alf