Excel - Combine data from multiple tables

Question

I have Excel 2016. I have three worksheets, each worksheet contains a table (lets call them tab1, tab2, tab3).

Each table contains a column called servId.

In the 4th worksheet I want to create a list of all the servId entries from all three tables.

e.g. if tab1 contains entries 1,2,3, tab2 contains 4,5,6, tab3 contains 7,8,9 I would have a resulting list of 1,2,3,4,5,6,7,8,9

How can I do that? I've found plenty of examples where you can match between tables, but that's no good as I want all the entries.

I tried the obvious:

=tab1[servId],tab2[servId],tab3[servId]

but that just gives me an error.

I'm sure there must be a really obvious way to do this, but can't for the life of me fifure it out.

BenderIO · Accepted Answer · 2018-06-13 10:29:30Z

1

I think that you can use the Query Editor for this.

1. In first, you must add all your tables to the Power Query Editor:

Select your first table, go to "Data>From Table"; it will open the Power Query Editor. Click on the black little arrow on "Close & Load" and choose "Close & Load to...". On the opened dialog box, change the default selection from "Table" to "Only Create Connection". Repeat this operation for all your table

2. Append all queries and keep only your servID:

Go to your new worksheet, go to "Data->New Query->Combine Queries->Appends", select "Three or more table" and add all the table. The Power Query Editor will open with all your table concatenate into one. You can make your transformation like keep only the ServID column (right-click on ServID column's name and Remove Other Columns for exemple).

When you have finish, use the normal "Close & Load"

And it's done :)

answered Jun 13, 2018 at 10:29

BenderIO

4124 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

IGGt Over a year ago

That is perfect (n.b. the New Query -> Combine Queries didn't exist. But right clicking on the Group brought up the Append Option).

Suraj Shourie · Accepted Answer · 2018-06-13 10:06:45Z

1

You can use a very simple macro to get the results.

excel tables

Sub Test()
    Range("Table1[ID]").Copy
    Range("J1").PasteSpecial xlPasteValues
    Range("Table2[ID]").Copy
    Total_Rows = WorksheetFunction.CountA(Range("J:J"))
    Range("J" & Total_Rows + 1).PasteSpecial xlPasteValues
    Range("Table3[ID]").Copy
    Total_Rows = WorksheetFunction.CountA(Range("J:J"))
    Range("J" & Total_Rows + 1).PasteSpecial xlPasteValues
    ActiveSheet.Range("$J:$J").RemoveDuplicates Columns:=1, Header:=xlNo
    Application.CutCopyMode = False

End Sub

excel after macro is run

This is assuming you don't have any data in column "J" at all and want the data pasted in the active sheet.

answered Jun 13, 2018 at 10:06

Suraj Shourie

2,60511 silver badges19 bronze badges

Collectives™ on Stack Overflow

Excel - Combine data from multiple tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related