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

Question

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?

You can use Power Query to merge 2 tables first and then remove duplicate. support.office.com/en-us/article/… and support.office.com/en-us/article/… — Lee, Commented Jan 11, 2019 at 1:35

p._phidot_ · Accepted Answer · 2019-01-10 10:00:05Z

2

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. ( :

answered Jan 10, 2019 at 10:00

p._phidot_

1,2731 gold badge8 silver badges14 bronze badges

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
Commented Jan 10, 2019 at 10:13
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_
Commented Jan 10, 2019 at 10:19
I reused your idea "Remove duplicate" to do this: superuser.com/a/1392701 What do you think?
– Basj
Commented Jan 10, 2019 at 10:51
1

Intuitively 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_
Commented Jan 10, 2019 at 10:57

Add a comment |

Basj · Accepted Answer · 2019-01-10 10:50:38Z

1

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.

answered Jan 10, 2019 at 10:50

Basj

1,91614 gold badges64 silver badges107 bronze badges

This is a lot more work and more error prone than the other answer
– Alex M
Commented Dec 20, 2019 at 20:13
@AlexM How so, can you give an example?
– Basj
Commented Dec 20, 2019 at 20:21
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_
Commented Dec 21, 2019 at 11:05

Add a comment |

2 Answers 2