F
Freedster
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
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