Help with Merge

[Jill]

Hi,

I have two worksheets in the same book.
Sheet 1
ID / Course / Description / Amount
01/PH 101 /Basic PR / $200
01/PH 102 /Inter PR / $50
02/GD 203 /Basic Gd / $150
03/L 301 /Travel / $250
03/SC 401 / Science /$100
etc...
Sheet 2
ID /Base /Addition /Total
01 /$250 /$0 /$250
02 /$150 /$0 /$150
03 /$100 /$250 /$350

What I want to create is:
ID / Course / Description / Amount /Base /Addition /Total
01/PH 101 /Basic PR / $200 /$250 /$0 /$250
01/PH 102 /Inter PR / $50
02/GD 203 /Basic Gd / $150 /$150 /$0 /$150
03/L 301 /Travel / $250 /$100 /$250 /$350
03/SC 401 / Science /$100

Please note the values in sheet 2 show up only once. Can I do this? How?

Thank you.

 

[FARAZ QURESHI]

Hi Jill,

Do you want to use the second sheet's data to be applied only on the first
time the ID appears on sheet1?

If yes, try the Vlookup formula as follows on your first sheet's fifth column:
=if(countif(A1:A$1,A1)>1,"",vlookup(A1,sheet2!$A$1:$D$3,2,0)
on Sixth column
=if(countif(A1:A$1,A1)>1,"",vlookup(A1,sheet2!$A$1:$D$3,3,0)

 

[Jill]

" Do you want to use the second sheet's data to be applied only on the first
time the ID appears on sheet1?"
Yes.

Before I try your suggestion, I have about 300 ID's. Does this mean I have
to type your formular 300 times?

 

[Jill]

I realize I misunderstood you. I will try what you suggested and report back.

Thank you.

 

[Jill]

I have another question. I have a lot more columns than I showed in my
example on both worksheets. What are the $D$3,2,0, $D$3,3,0 etc represent?

 

[FARAZ QURESHI]

The 2, 3, and 4 represent the column numbers of sheet2 you want to return back.

 

[FARAZ QURESHI]

If you want to apply on every ID simply enter:
=vlookup(A1,sheet2!$A$1:$D$3,2,0)
instead of
=if(countif(A1:A$1,A1)>1,"",vlookup(A1,sheet2!$A$1:$D$3,2,0))

 

[FARAZ QURESHI]

In a case you have many columns on sheet2 remember to change the
sheet2!$A$1:$D$3 to cover up the complete data range on sheet2.

Best o' luck.

 

[Jill]

It worked. It did exactly what I wanted to do. Glad to learn a new trick.

Thank you for your help.