Comparing Two Lists and Deleting Unmatched Items

6

65Stang

I have two excel "lists" that I want to merge and delete unmatche
items. The first "list" has a column with employee#'s for anyone wh
is eligible for insurance. The second "list" has employee#'s, employe
names, and hours worked for the month. What function should I use?

Thanks for your help
 
M

Max

One way ..

Assuming you have

In Sheet2 (the 2nd list) in A1:C10
--------------
Emp# EmpN HrsW
100 Name1 20
101 Name2 16
102 Name3 20
103 Name4 16
104 Name5 16
105 Name6 8
106 Name7 17
107 Name8 15
108 Name9 19

which is to be merged/matched with ..

In Sheet1 (the 1st list - insurance eligibles) in A1:A7
-------------
Emp# EmpN HrsW
100
102
104
105
107
108

Put in B2:

=IF(ISNA(MATCH($A2,Sheet2!$A:$A,0)),"",OFFSET(Sheet2!$A$1,MATCH($A2,Sheet2!$
A:$A,0)-1,COLUMN(A1)))

Copy down to B7, fill across to C7 to populate the table

Any unmatched Emp#s will return blanks: "" in cols B and C

And if you need to remove unmatched Emp#s,
just do an autofilter on say col B:
Select (blanks) from the droplist and delete the filtered rows
 
Top