0

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?

1

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 :

  1. Copy both ID column and manually stack it > Then select (the combined column) > Data > Remove duplicate. [ now you have your column 1 ]

  2. fill in the 'VALUE 1' , 'VALUE 2' text in B1 & C1 as the header.

  3. 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

  4. drag both cell downwards.

That should work. Have a try. ( :

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

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

  1. Copy the IDs from Worksheet #2 at the end of the ID column of Worksheet #1. Then go to menu Data > Remove duplicate.

  2. Copy the IDs from Worksheet #1 at the end of the ID column of Worksheet #2. Then go to menu Data > Remove duplicate.

  3. Then sort (ascending) each worksheet based on the ID column. Now the 2 worksheets have the exact same IDs!

  4. Now we can copy / paste data from both worksheets since the IDs are the same, in the same order.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.