Arouet
05-14-2008, 11:48 AM
I'm trying to take a office personnel file and determine if the employees are still active, retired, or unlisted in the building personnel file.. I've tried my best using vlookups, but can't solve it because the names change formats. The names in the office list are in Row A and I want to add a 'Status' Column in for H.
File Formats:
Office List
[Column A]:
[Last, First]
"Smith, John"
Active List
[Column A]:
[Last, First M.I.]
"Smith, John J"
Retired List
[Column A] [Column B]:
[Last] [First M.I.]
["Smith"] ["John J"]
If the names were exact, I believe this would work.
=IF(ISERROR(VLOOKUP(A1,[activelist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
IF(ISERROR(VLOOKUP(A1,[retiredlist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
"Not Found", "Retired"), "Active")
I don't know if there are ways to alter the formula, but I figured I'd have to use macro's. Any help? I figure it should look something like this: I've dabbled in C++ but never VBA
for each name in office
found = "false"
status = "not found"
for each name in active (while found = false)
if names are equal
found = true
status = "active"
for each name in retired (while found = false)
if names are equal
found = true
status = "retired"
Thank you very much.
File Formats:
Office List
[Column A]:
[Last, First]
"Smith, John"
Active List
[Column A]:
[Last, First M.I.]
"Smith, John J"
Retired List
[Column A] [Column B]:
[Last] [First M.I.]
["Smith"] ["John J"]
If the names were exact, I believe this would work.
=IF(ISERROR(VLOOKUP(A1,[activelist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
IF(ISERROR(VLOOKUP(A1,[retiredlist.xls]Sheet1!$A$3:$A$400,1,FALSE)),
"Not Found", "Retired"), "Active")
I don't know if there are ways to alter the formula, but I figured I'd have to use macro's. Any help? I figure it should look something like this: I've dabbled in C++ but never VBA
for each name in office
found = "false"
status = "not found"
for each name in active (while found = false)
if names are equal
found = true
status = "active"
for each name in retired (while found = false)
if names are equal
found = true
status = "retired"
Thank you very much.