| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Trying to merge from excel to word. New names won't merge | Excel Worksheet Functions | |||
| Merge option is not available. How to undo merge in this case? | Excel Discussion (Misc queries) | |||
| mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
| Merge =( formula should retain fraction type numbers after merge. | Excel Worksheet Functions | |||
| how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
Linear Mode
