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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 12:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com