Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with duplicate unique items
I have a one master file with a worksheet like below that I send out to
multiple people,it's about 2k rows long: A B C D 1 40921 John Booked Low risk 2 40922 John Booked 3 40923 John 4 40924 Peter 5 40925 Peter 6 40925 Peter What I tried doing is copying all the worksheets that I get into one and using a Vlookup to pull data from columns C and D into one sheet, but with vlookup it pulls the first piece of data it finds so sometimes I end up with a blank when I know data is present. Does anyone know of a formula that can fit my needs? JS |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with duplicate unique items
If your unique combination is 40921 AND JOHN then combine them in one COL by
=A1&B1 If you do that after inserting a Col after COL B (in Sheet2) then use this for lookup =VLOOKUP(A1&B1, Sheet2!C:E,3,FALSE) "John" wrote: I have a one master file with a worksheet like below that I send out to multiple people,it's about 2k rows long: A B C D 1 40921 John Booked Low risk 2 40922 John Booked 3 40923 John 4 40924 Peter 5 40925 Peter 6 40925 Peter What I tried doing is copying all the worksheets that I get into one and using a Vlookup to pull data from columns C and D into one sheet, but with vlookup it pulls the first piece of data it finds so sometimes I end up with a blank when I know data is present. Does anyone know of a formula that can fit my needs? JS |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with duplicate unique items
I tried that, but when I consolidate all of these worksheets into one, i have
more than one piece of unique data, so for example: 40921 John 40921 John 40921 John Booked Low Risk 40924 Peter 40924 Peter Low Risk 40924 Peter 40925 Lillian 40925 Lillian Booked 40925 So when I run the vlookup it returns nothing because it picks the first one it finds. "Sheeloo" wrote: If your unique combination is 40921 AND JOHN then combine them in one COL by =A1&B1 If you do that after inserting a Col after COL B (in Sheet2) then use this for lookup =VLOOKUP(A1&B1, Sheet2!C:E,3,FALSE) "John" wrote: I have a one master file with a worksheet like below that I send out to multiple people,it's about 2k rows long: A B C D 1 40921 John Booked Low risk 2 40922 John Booked 3 40923 John 4 40924 Peter 5 40925 Peter 6 40925 Peter What I tried doing is copying all the worksheets that I get into one and using a Vlookup to pull data from columns C and D into one sheet, but with vlookup it pulls the first piece of data it finds so sometimes I end up with a blank when I know data is present. Does anyone know of a formula that can fit my needs? JS |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with duplicate unique items
Sort DESCENDING on Col C & D so filled rows come first, then your lookup will
find them "John" wrote: I tried that, but when I consolidate all of these worksheets into one, i have more than one piece of unique data, so for example: 40921 John 40921 John 40921 John Booked Low Risk 40924 Peter 40924 Peter Low Risk 40924 Peter 40925 Lillian 40925 Lillian Booked 40925 So when I run the vlookup it returns nothing because it picks the first one it finds. "Sheeloo" wrote: If your unique combination is 40921 AND JOHN then combine them in one COL by =A1&B1 If you do that after inserting a Col after COL B (in Sheet2) then use this for lookup =VLOOKUP(A1&B1, Sheet2!C:E,3,FALSE) "John" wrote: I have a one master file with a worksheet like below that I send out to multiple people,it's about 2k rows long: A B C D 1 40921 John Booked Low risk 2 40922 John Booked 3 40923 John 4 40924 Peter 5 40925 Peter 6 40925 Peter What I tried doing is copying all the worksheets that I get into one and using a Vlookup to pull data from columns C and D into one sheet, but with vlookup it pulls the first piece of data it finds so sometimes I end up with a blank when I know data is present. Does anyone know of a formula that can fit my needs? JS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display only duplicate values and delete UNIQUE Items | Excel Discussion (Misc queries) | |||
Duplicate items | Excel Discussion (Misc queries) | |||
selecting unique items | Excel Worksheet Functions | |||
Duplicate and unique items in 2 lists | Excel Discussion (Misc queries) | |||
How to get rid off duplicate items? | Excel Discussion (Misc queries) |