Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
This is an asset managment issue, in a way. I've got two sheets I'm trying
to aggregate to figure out what department owns what servers. On one sheet I've got servernames, and the first name and last name of the owners. For what it's worth, some servers have multiple owners. This has about 400 records. On the second sheet I've exported the entire company directory. I've got several fields, but I've parsed and normalized things out so I have the first and last names, the departments they work for, and who they report to. This has several thousand records and I can sort it however I like. My problem is that if I vlookup() by first name or last name for a given server, I'm inevitably going to get multiple people with that same first or last name that aren't the owners. In other words, if I vlookup() "Larry Jones" by his first or last name, I'll get 3 Larry's and 8 Jones's. What I really need to do is vlookup() the pair together; somehow vlookup() Larry among the Jones's (or vice versa), and cross reference his deparment number, etc. with Larry Jones's server. Caveats: - I can do whatever I want with this data to manipulate it as needed to make this easier. - There is a "full name" field in the company directory, but it's not normalized and in cases it may or may not have a middle initial. Simply combining "first name" and "last name" on the server sheet won't equate to the "full name" field on the directory sheet. - We can assume that there's no two people with the same first and last names. I'm going to try to combine the first names and last names on both sheets with a column of text() cells to see if that works, but I'd still be curious how something like this could be accomplished with just lookups and without adding data. Thanks in advance for any advice, - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
OK, combining the names seemed to have worked, and I have the info I need. I
did a simple =C2&" "&D2 column on each sheet to append the names together and then I was able to vlookup() against that for my departments, etc. I'm still curious though if there's a way to do it without adding columns to both sheets, though. No rush. :) - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training "Freedster" wrote: This is an asset managment issue, in a way. I've got two sheets I'm trying to aggregate to figure out what department owns what servers. On one sheet I've got servernames, and the first name and last name of the owners. For what it's worth, some servers have multiple owners. This has about 400 records. On the second sheet I've exported the entire company directory. I've got several fields, but I've parsed and normalized things out so I have the first and last names, the departments they work for, and who they report to. This has several thousand records and I can sort it however I like. My problem is that if I vlookup() by first name or last name for a given server, I'm inevitably going to get multiple people with that same first or last name that aren't the owners. In other words, if I vlookup() "Larry Jones" by his first or last name, I'll get 3 Larry's and 8 Jones's. What I really need to do is vlookup() the pair together; somehow vlookup() Larry among the Jones's (or vice versa), and cross reference his deparment number, etc. with Larry Jones's server. Caveats: - I can do whatever I want with this data to manipulate it as needed to make this easier. - There is a "full name" field in the company directory, but it's not normalized and in cases it may or may not have a middle initial. Simply combining "first name" and "last name" on the server sheet won't equate to the "full name" field on the directory sheet. - We can assume that there's no two people with the same first and last names. I'm going to try to combine the first names and last names on both sheets with a column of text() cells to see if that works, but I'd still be curious how something like this could be accomplished with just lookups and without adding data. Thanks in advance for any advice, - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
On Dec 5, 10:23*am, Freedster
wrote: OK, combining the names seemed to have worked, and I have the info I need.. *I did a simple =C2&" "&D2 column on each sheet to append the names together and then I was able to vlookup() against that for my departments, etc. I'm still curious though if there's a way to do it without adding columns to both sheets, though. *No rush. *:) Try putting that right in your VLOOKUP instead of in a column. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
Hi,
Try this =INDEX(I1:I3,SUMPRODUCT(--(A1=G1:G3),--(B1=H1:H3),ROW(H1:H3))) Where the First Name of the individual is in A1 and is listed in G1:G3, and Last Name in B1 and somewhere in H1:H3, I1:I3 has the listing of the departments. Just one point - first and last names together are not a good primary key, there could be duplicates of the combination. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Freedster" wrote: OK, combining the names seemed to have worked, and I have the info I need. I did a simple =C2&" "&D2 column on each sheet to append the names together and then I was able to vlookup() against that for my departments, etc. I'm still curious though if there's a way to do it without adding columns to both sheets, though. No rush. :) - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training "Freedster" wrote: This is an asset managment issue, in a way. I've got two sheets I'm trying to aggregate to figure out what department owns what servers. On one sheet I've got servernames, and the first name and last name of the owners. For what it's worth, some servers have multiple owners. This has about 400 records. On the second sheet I've exported the entire company directory. I've got several fields, but I've parsed and normalized things out so I have the first and last names, the departments they work for, and who they report to. This has several thousand records and I can sort it however I like. My problem is that if I vlookup() by first name or last name for a given server, I'm inevitably going to get multiple people with that same first or last name that aren't the owners. In other words, if I vlookup() "Larry Jones" by his first or last name, I'll get 3 Larry's and 8 Jones's. What I really need to do is vlookup() the pair together; somehow vlookup() Larry among the Jones's (or vice versa), and cross reference his deparment number, etc. with Larry Jones's server. Caveats: - I can do whatever I want with this data to manipulate it as needed to make this easier. - There is a "full name" field in the company directory, but it's not normalized and in cases it may or may not have a middle initial. Simply combining "first name" and "last name" on the server sheet won't equate to the "full name" field on the directory sheet. - We can assume that there's no two people with the same first and last names. I'm going to try to combine the first names and last names on both sheets with a column of text() cells to see if that works, but I'd still be curious how something like this could be accomplished with just lookups and without adding data. Thanks in advance for any advice, - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
Yeah, I know it's not a good primary key. I personally know of several
people with approximately the same name in the company (Ron & Ronnie, or James & Jamie). I also know that this isn't the case with any of the names associated with the servers, though. This is kind of a one time "maintenance and current status" spreadsheet. It helped me find about 50 servers on the list that now need to be examined and updated, and that was kind of the point. It's not a permanent recurring work item, otherwise, I'd worry more about the potential for duplicate keys. Thanks again for the tips! -- - Freed Business Continuity Planner Excel Wizard-in-training "Shane Devenshire" wrote: Hi, Try this =INDEX(I1:I3,SUMPRODUCT(--(A1=G1:G3),--(B1=H1:H3),ROW(H1:H3))) Where the First Name of the individual is in A1 and is listed in G1:G3, and Last Name in B1 and somewhere in H1:H3, I1:I3 has the listing of the departments. Just one point - first and last names together are not a good primary key, there could be duplicates of the combination. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Freedster" wrote: OK, combining the names seemed to have worked, and I have the info I need. I did a simple =C2&" "&D2 column on each sheet to append the names together and then I was able to vlookup() against that for my departments, etc. I'm still curious though if there's a way to do it without adding columns to both sheets, though. No rush. :) - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training "Freedster" wrote: This is an asset managment issue, in a way. I've got two sheets I'm trying to aggregate to figure out what department owns what servers. On one sheet I've got servernames, and the first name and last name of the owners. For what it's worth, some servers have multiple owners. This has about 400 records. On the second sheet I've exported the entire company directory. I've got several fields, but I've parsed and normalized things out so I have the first and last names, the departments they work for, and who they report to. This has several thousand records and I can sort it however I like. My problem is that if I vlookup() by first name or last name for a given server, I'm inevitably going to get multiple people with that same first or last name that aren't the owners. In other words, if I vlookup() "Larry Jones" by his first or last name, I'll get 3 Larry's and 8 Jones's. What I really need to do is vlookup() the pair together; somehow vlookup() Larry among the Jones's (or vice versa), and cross reference his deparment number, etc. with Larry Jones's server. Caveats: - I can do whatever I want with this data to manipulate it as needed to make this easier. - There is a "full name" field in the company directory, but it's not normalized and in cases it may or may not have a middle initial. Simply combining "first name" and "last name" on the server sheet won't equate to the "full name" field on the directory sheet. - We can assume that there's no two people with the same first and last names. I'm going to try to combine the first names and last names on both sheets with a column of text() cells to see if that works, but I'd still be curious how something like this could be accomplished with just lookups and without adding data. Thanks in advance for any advice, - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and multiple matches | Excel Discussion (Misc queries) | |||
Vlookup to return the sum of multiple matches | Excel Discussion (Misc queries) | |||
How do I get multiple matches when using the Vlookup function? | Excel Worksheet Functions | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions | |||
how to deal with multiple matches on vlookup? | Excel Discussion (Misc queries) |