Combining data from two tables in Excel into one new table

Copper Contributor

Have been struggling for hours with a task in Excel that should be very simple.

 

I have two tables in Excel in two sheets. They are names tblEx and tblSales, and each have unique records.

 

I want to combine these tables into a third table, which contains all the records from the first two tables. In the third sheet "combined table" is the table I want to get. I want to do this without copy and paste, as I need to do it for a large amount of data. (The attached workbook is just an example with a small amount of dummy data.)

 

Anyone knows how to do this? Can't believe that there is no function for this in Excel, but I have not been able to find any.

 

 

5 Replies

Hi @PeterW1 

 

There are different ways to handle it, I will be sharing you two solution & see what suites best for you

 

Solution 01

You can use this VBA code to combine all the data into your workbook.

Combine Active Workbook All Sheets into One Master Sheet with VBA | Excel Exciting

 

Note: looking the structure of your data note that code will break as the column A is empty. Start from the from A1

 

Solution 02 - [Excel 2010 & Above] 

This dynamic solution 

1. Convert your data into Excel Table (ctrl + t)

2. Give you table name 

3. Go to Data > GET Data > From Other Source > Blank Query

4. Power Query Editor

5. Incase if your formula bar is not enabling then click on VIEW > Layout > check the formula bar

6. In the formula bar enter "= Excel.CurrentWorkbook()" 

Note that this case sensitive

7. Select the table you like to combine

8. click on content expand table 

 

You will have all your records at one place. this is recommended solution

 

Note: you make your table header same before combining the records for both the solutions

 

Attached is the sample file.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

Hi @Faraz Shaikh 

 

Thank you very much for replying.

 

I tried your Solution 02.

But the only options I have for getting data from other source is From HTML, From Text, New Database Query [SQL Server ODBC, From Database]. there is no option for Blank Query.

 

I should probably have mentioned that I work in Excel 365 for Mac.

 

Peter

@PeterW1 

Yes, Power Query on Mac is quite limited. 

@PeterW1 

 

Then you need go with the solution number 01, as rightly said by @Sergei Baklan currently Power Query for mac is limited. 

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

 

@Faraz ShaikhI have a problem of combining two tables from two different workbooks.

 

I managed to solvethe first problem which is bringing the data based on the word "active"

Option Explicit

Sub Test()

Dim Cell As Range

With Sheets(1)
    ' loop column H untill last cell with value (not entire column)
    For Each Cell In .Range("G1:G" & .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "Active" Then
             ' Copy>>Paste in 1-line (no need to use Select)
            .Rows(Cell.Row).Copy Destination:=Sheets(4).Rows(Cell.Row)
            
        End If
    Next Cell End With

End Sub

However, I have three issues.
1)There is empty rows in between that was not active I want to delete them.
2)Based on the ID number that is associated with active word, the macro needs to find the ID in the second workbook and copy the row to the new table.
3)There is empty cells, or errors like#N/A, the code needs to return back to me.
Any suggestions how to fix my code in VBA?

I appreciate your help

Best Regards