Comparing Entries to a list

[Davegrier]

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

 

[Max]

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

---

 

[Davegrier]

Works like a charm, thanks for the help Max!

 

[Max]

Pleased to hear that !
Thanks for the feedback ..