ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Help with Merge (https://www.excelbanter.com/excel-discussion-misc-queries/232977-help-merge.html)

Jill
Help with Merge
 
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
Help with Merge
 
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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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.

Jill
Help with Merge
 
" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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.

Jill
Help with Merge
 
I realize I misunderstood you. I will try what you suggested and report back.

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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.

Jill
Help with Merge
 
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?

"Jill" wrote:

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

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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
Help with Merge
 
The 2, 3, and 4 represent the column numbers of sheet2 you want to return back.

"Jill" wrote:

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?

"Jill" wrote:

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

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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
Help with Merge
 
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))

"Jill" wrote:

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?

"Jill" wrote:

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

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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
Help with Merge
 
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.

"FARAZ QURESHI" wrote:

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

"Jill" wrote:

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?

"Jill" wrote:

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

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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.

Jill
Help with Merge
 
It worked. It did exactly what I wanted to do. Glad to learn a new trick.

Thank you for your help.

"FARAZ QURESHI" wrote:

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.

"FARAZ QURESHI" wrote:

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

"Jill" wrote:

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?

"Jill" wrote:

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

Thank you.

"Jill" wrote:

" 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?


"FARAZ QURESHI" wrote:

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)
on Seventh
=if(countif(A1:A$1,A1)1,"",vlookup(A1,sheet2!$A$1 :$D$3,4,0)


"Jill" wrote:

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.


All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com