CODEWITHSUNDEEP
excelindexingfiltervlookup
bvowe
bvowe

Reputation: 3384

Excel MERGE two tables

I have SET 1

CLASS Student TEST SCORE
A 1 1 46
A 1 2 50
A 1 3 45
A 2 1 45
A 2 2 47
A 2 3 31
A 3 1 34
A 3 2 45
B 1 1 36
B 2 1 31
B 2 2 41
B 3 1 50
C 1 1 42
C 3 1 31

and SET 2

CLASS SIZE YEARS
A 39 7
B 20 12
C 31 6

and wish to COMBINE to make SET 3

CLASS STUDENT TEST SCORE SIZE YEARS
A 1 1 46 39 7
A 1 2 50 39 7
A 1 3 45 39 7
A 2 1 45 39 7
A 2 2 47 39 7
A 2 3 31 39 7
A 3 1 34 39 7
A 3 2 45 39 7
B 1 1 36 20 12
B 2 1 31 20 12
B 2 2 41 20 12
B 3 1 50 20 12
C 1 1 42 31 6
C 3 1 31 31 6

so basically add the SIZE and YEARS columns from SET 2 and merge on CLASS onto SET 1. In excel how you can do this? I need to match on CLASS

Upvotes: 0

Views: 3118

Answers (4)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

This solution may work for you if both sets start in the same column. As example in my image, both of them start at column A. You can get all data with a single VLOOKUP formula:

enter image description here

Formula in cell E2 is:

=VLOOKUP($A2;$A$22:$R$25;COLUMN($B22);FALSE)

Notice the mixed references at first and third argument and absolute references in the second one. Third argument is critical, because is the relational position between both sets, that's the reason it's easier if both sets start at same column. If not, you'll need to adjust this argument substracting or adding, depending on the case.

Anyways, with a single formula, you can get any number of columns. The only disavantage of this formula is that you need to manually drag to right until you got all the columns (10, 30 or whatever). You'll notice you are done because the formula will raise an error:

enter image description here

This error means you are trying to get a referenced outside of your column area.

Upvotes: 0

Anonymous
Anonymous

Reputation: 379

Define both sets as tables and “left join” in PowerQuery. There you can choose the columns of the resulting table.

https://learn.microsoft.com/en-us/power-query/merge-queries-left-outer

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9917

Your first set of data is essentially your primary set of data that you just want to add attribute columns to. I built this example on Google Sheets which should help explain. Using spill formulas, only a few cells are needed with their own formulas. You can see them as they are highlighted in yellow. When you use in Excel, obviously make sure you change the column references, but this would get you the answer.

Note you have to have SpillRange in Excel for this to work. To test, see if you have the formula =unique()

enter image description here

Upvotes: 0

Johannes Riecken
Johannes Riecken

Reputation: 2515

If you have Set 1 on the top left of a worksheet "Set1" and Set 2 on the top left of a worksheet "Set2", then you can use the formula =VLOOKUP(A2;'Set2'!$A$2:$C$4;2;FALSE), where $A$2:$C$4 is the range of Set2, and A2 is the class value from Set1, which is what is used to do the lookup in Set2. The next argument, 2, means to take the second row from Set2, and the FALSE at the end means that you only want exact matches on the CLASS. You can do auto-fill with this formula, and do similar steps for the years. If you look up the help for VLOOKUP within Excel, that should help you to understand how it works.

Upvotes: 0

Related Questions