Combining data from two tables in Excel into one new table

Question

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.

excelexciting · Answer

Hi PeterW1&nbsp;&nbsp;There are different ways to handle it, I will be sharing you two solution &amp; see what suites best for you&nbsp;Solution 01You 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&nbsp;Note: looking the structure of your data note that code will break as the column A is empty. Start from the from A1&nbsp;Solution 02 - [Excel 2010 &amp; Above]&nbsp;This dynamic solution&nbsp;1. Convert your data into Excel Table (ctrl + t)2. Give you table name&nbsp;3. Go to Data &gt; GET Data &gt; From Other Source &gt; Blank Query4. Power&nbsp;Query Editor5. Incase if your formula bar is not enabling then click on VIEW &gt; Layout &gt; check the formula bar6. In the formula bar enter "= Excel.CurrentWorkbook()"&nbsp;Note that this case sensitive7. Select the table you like to combine8. click on content expand table&nbsp;&nbsp;You will have all your records at one place. this is recommended solution&nbsp;Note: you make your table header same before combining the records for both the solutions&nbsp;Attached is the sample file.&nbsp;Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel ExpertIf you find the above solution resolved your query don't forget mark as&nbsp;Official/Best Answer &amp; like it to help the other members find it more.

peterw1 · Answer

Hi&nbsp;ExcelExciting&nbsp;&nbsp;Thank you very much for replying.&nbsp;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.&nbsp;I should probably have mentioned that I work in Excel 365 for Mac.&nbsp;Peter

sergeibaklan · Answer

PeterW1&nbsp;
Yes, Power Query on Mac is quite limited.&nbsp;

excelexciting · Answer

PeterW1&nbsp;&nbsp;Then you need go with the solution number 01, as&nbsp;rightly said by&nbsp;SergeiBaklan&nbsp;currently Power Query for mac is limited.&nbsp;&nbsp;Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel ExpertIf you find the above solution resolved your query don't forget mark as&nbsp;Official/Best Answer &amp; like it to help the other members find it more.&nbsp;

nena99 · Answer

ExcelExcitingI have a problem of combining two tables from two different workbooks.&nbsp;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" &amp; .Cells(.Rows.Count, "H").End(xlUp).Row)
        If Cell.Value = "Active" Then
             ' Copy&gt;&gt;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

Forum Discussion

Combining data from two tables in Excel into one new table

5 Replies

Resources