Dec 01 2020 03:11 AM
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.
Dec 01 2020 05:11 AM
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.
Dec 02 2020 02:34 AM
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
Dec 02 2020 02:52 AM
Yes, Power Query on Mac is quite limited.
Dec 02 2020 04:31 AM
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.
Apr 08 2022 07:03 AM
@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