How to merge Excel worksheets by stacking columns, using an ID column?

I have a Worksheet #1 containing:

ID        VALUE1
----------------
ABC       8274
DEF       613
GHI       236

and a Worksheet #2 containing:

ID        VALUE2
----------------
ABC       11.13
XYZ       173   
GHI       999

I'd like to merge them and have:

ID    VALUE1   VALUE2
---------------------
ABC   8274     11.13
DEF   613
GHI   236      999
XYZ            173

How to do such a merging with Excel 2007?

Basj

Posted 2019-01-10T08:29:37.667

Reputation: 1 356

You can use Power Query to merge 2 tables first and then remove duplicate. https://support.office.com/en-us/article/Merge-queries-Power-Query-FD157620-5470-4C0F-B132-7CA2616D17F9 and https://support.office.com/en-us/article/Remove-duplicates-Power-Query-d9cffc69-dc5d-4d94-8b66-72779688874d

– Lee – 2019-01-11T01:35:22.643

Answers

I reused the Data > Remove duplicate idea from @p.phidot's answer to do it this way:

Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.
Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.
Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!
Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.

Basj

Posted 2019-01-10T08:29:37.667

Reputation: 1 356

This is a lot more work and more error prone than the other answer – Alex M – 2019-12-20T20:13:37.783

@AlexM How so, can you give an example? – Basj – 2019-12-20T20:21:23.433

what @AlexM meant was.. if there is an extra/missing/additional in both/either one of the worksheet, the sort doesn't promise an accurate combined listing anymore.. ( : – p._phidot_ – 2019-12-21T11:05:53.247

TLDR : filter unique ID, then use index match + iferror() .

Assuming all you data header starts at cell A1 of each sheet. Put 'ID' text in cell A1 of a new sheet.. then :

Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]
fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.
put =IFERROR(INDEX(Sheet1!$B:$B,MATCH($A2,Sheet1!$A:$A,0)),"") in B2 & =IFERROR(INDEX(Sheet2!$B:$B,MATCH($A2,Sheet2!$A:$A,0)),"") in C2
drag both cell downwards.

That should work. Have a try. ( :

p._phidot_

Posted 2019-01-10T08:29:37.667

Reputation: 948

Thank you for your answer @p.phidot. I'll try this! BTW, would there be a way without such code, but only using menus / commands like - I don't know what it is! - "pivot table"? – Basj – 2019-01-10T10:13:57.057

AFAIK, nope. || Nonetheless, I don't know if some excel plugins can do this feature. Just suggesting.. but IMHO, this kind of thing (Excel plugin) is as close as a point-and-click option can get. ( :

– p._phidot_ – 2019-01-10T10:19:49.113

I reused your idea "Remove duplicate" to do this: https://superuser.com/a/1392701 What do you think?

– Basj – 2019-01-10T10:51:03.217

1Intuitively inspiring.. || That's what ring in my head when I read it.. Good job.. a good point-and-click option.. I like it. /(^_^) – p._phidot_ – 2019-01-10T10:57:22.020