Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Entries to a list
Hello, great board you have here. My question is this: I have a static list of employees, start dates, and supervisors in 3 columns: Bob Smith | 01/06/94 | Jones Jen Baker | 05/06/03 | Anderson etc. I need to compare this static list to a new list I receive daily of those employees that worked each day. I have the list of employees (in a standard repeatable format, the names will not vary in case or spelling) and I need to fill in the start date and supervisor in 2 columns next to the name. I have a template already, so I can keep my static master list on a separate worksheet, but I'm not sure how to compare one list to another and extract the relevant data. Thanks for the help! Dave Grier -- Davegrier ------------------------------------------------------------------------ Davegrier's Profile: http://www.excelforum.com/member.php...o&userid=31171 View this thread: http://www.excelforum.com/showthread...hreadid=508309 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Entries to a list
One way via VLOOKUP ..
Assuming the static list is in Sheet1, cols A to C, data from row2 down (Headers in A1:C1 : Emp, StartDate, Sup) Bob Smith | 01/06/94 | Jones Jen Baker | 05/06/03 | Anderson etc. and the new list is in Sheet2, cols A to C, data from row2 down (Same headers in A1:C1 : Emp, StartDate, Sup) With the emp names listed in A2 down, to extract the StartDate & Sup, Put in B2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B1), 0)) Copy B2 to C2, fill down as far as required Format col B as date to taste Perhaps better (but longer) with a dash more error-trapping, we could put instead in B2: =IF($A2="","",IF(ISNA(MATCH($A2,Sheet1!$A:$A,0))," No match found",VLOOKUP($A2,Sheet1!$A:$C,COLUMN(B1),0))) and copy across to C2, fill down as before The above would return the phrase: "No match found" for any unmatched Emp names instead of "ugly" #N/As -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Davegrier" wrote in message ... Hello, great board you have here. My question is this: I have a static list of employees, start dates, and supervisors in 3 columns: Bob Smith | 01/06/94 | Jones Jen Baker | 05/06/03 | Anderson etc. I need to compare this static list to a new list I receive daily of those employees that worked each day. I have the list of employees (in a standard repeatable format, the names will not vary in case or spelling) and I need to fill in the start date and supervisor in 2 columns next to the name. I have a template already, so I can keep my static master list on a separate worksheet, but I'm not sure how to compare one list to another and extract the relevant data. Thanks for the help! Dave Grier -- Davegrier ------------------------------------------------------------------------ Davegrier's Profile: http://www.excelforum.com/member.php...o&userid=31171 View this thread: http://www.excelforum.com/showthread...hreadid=508309 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Entries to a list
Works like a charm, thanks for the help Max! -- Davegrier ------------------------------------------------------------------------ Davegrier's Profile: http://www.excelforum.com/member.php...o&userid=31171 View this thread: http://www.excelforum.com/showthread...hreadid=508309 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparing Entries to a list
Pleased to hear that !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Davegrier" wrote in message ... Works like a charm, thanks for the help Max! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best way to get a list of unique entries in a field | Excel Worksheet Functions | |||
update data validation list with new entries?? | Excel Discussion (Misc queries) | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
comparing columns | Excel Discussion (Misc queries) | |||
How do I Select Multiple entries from Valid list for a Cell | Excel Discussion (Misc queries) |