• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy paste identical columns in two Excel files next to each other

wollyka

Member
Hi
I have 2 excel files (file 1 and file 2)with the same number of columns and the same headings. However, file 1 contains codes and file 2 has labels or text.
I want to create a macro that compare the headings of those 2 files, then for each identical column heading in file 2, copy paste the entire column and paste it next to its identical column in file 1 so i can have in one excel file one column with the codes and next to it the column with the labels.

Thanks
 
Hi
I uploaded some mock-up files- File 1 with the codes, File 2 with the labels and combined is the required results. Sometimes the order of the headings in File 1 or 2 differ.
 

Attachments

  • Combined.xlsx
    9.3 KB · Views: 1
  • File 2.xlsx
    8.9 KB · Views: 2
  • File 1.xlsx
    8.3 KB · Views: 1
I used Power Query and joined the two files into a single file. See attached.

Here is the Mcode to make this happen.

Code:
let
    Source = Excel.Workbook(File.Contents("C:\Users\alans\OneDrive\Desktop\File 1.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"FW_Name", type text}, {"D13", Int64.Type}, {"D14", type text}, {"D15", Int64.Type}, {"D16", Int64.Type}, {"D17", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"FW_Name"},Test_Basic,{"FW_Name"},"Test_Basic",JoinKind.FullOuter),
    #"Expanded Test_Basic" = Table.ExpandTableColumn(#"Merged Queries", "Test_Basic", {"FW_Name", "D13", "D14", "D15", "D16", "D17", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16"}, {"Test_Basic.FW_Name", "Test_Basic.D13", "Test_Basic.D14", "Test_Basic.D15", "Test_Basic.D16", "Test_Basic.D17", "Test_Basic.Column7", "Test_Basic.Column8", "Test_Basic.Column9", "Test_Basic.Column10", "Test_Basic.Column11", "Test_Basic.Column12", "Test_Basic.Column13", "Test_Basic.Column14", "Test_Basic.Column15", "Test_Basic.Column16"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Test_Basic",{"FW_Name", "Test_Basic.FW_Name", "Test_Basic.D13", "Test_Basic.D14", "Test_Basic.D15", "Test_Basic.D16", "Test_Basic.D17", "Test_Basic.Column7", "Test_Basic.Column8", "Test_Basic.Column9", "Test_Basic.Column10", "Test_Basic.Column11", "Test_Basic.Column12", "Test_Basic.Column13", "Test_Basic.Column14", "Test_Basic.Column15", "Test_Basic.Column16", "D13", "D14", "D15", "D16", "D17"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Test_Basic.Column7", "Test_Basic.Column8", "Test_Basic.Column9", "Test_Basic.Column10", "Test_Basic.Column11", "Test_Basic.Column12", "Test_Basic.Column13", "Test_Basic.Column14", "Test_Basic.Column15", "Test_Basic.Column16"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"FW_Name", "Test_Basic.FW_Name", "Test_Basic.D13", "D13", "D14", "Test_Basic.D14", "D15", "Test_Basic.D15", "D16", "Test_Basic.D16", "D17", "Test_Basic.D17"})
in
    #"Reordered Columns1"
 

Attachments

  • Book2.xlsx
    24.5 KB · Views: 1
Thanks but excuse my question since i never used power query before. From what i understand from the code, I have to change the code and rename/add the columns each time i have new files with different headings, right?
Some files will have hundred of columns (big excel files) so it may take some time right?
Thanks
 
Once the query is established and new data is entered into the source file(s), all you really need to do is refresh the query. When you refresh, you will need to click on the refresh button two times.
 
Back
Top