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:

ABC   8274     11.13
DEF   613
GHI   236      999
XYZ            173   

How to do such a merging with Excel 2007?


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

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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