How to compare two columns of Names

S

Sharon

I have two columns of names, original list vs new list. I want to find the
names in the new list that do not appear in the original lis.t
 
D

Don Guillett

One simplified way.

Sub findem()
For Each c In Range("b2:b6")
If Range("a2:a22").Find(c) Is Nothing Then MsgBox c & "not there"
Next c
End Sub
 
P

Pete_UK

Suppose you have a list of names in column A of two sheets in the same
workbook. Put this formula in B1 of Sheet1:

=IF(ISNA(MATCH(A1,Sheet2!A:A,0)),"unique","Duplicated")

and copy it down to the bottom of your list.

You could also put this formula in B1 of Sheet2:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"unique","Duplicated")

and copy that down.

Then you can apply a filter to column B and select Duplicated from the
filter pull-down.

Hope this helps.

Pete
 
F

Farmer Ted

Pete, thanks. that works....I was wondering is there a way to pull a line
out (say column c1) (Tab1) and have it apear in tab 2 if there is a
duplicate? So If Both Tab 1 and Tab 2 have a duplicate a value in Column C1
will be moved over to sheet 2 (Tab 2)?
 
P

Pete_UK

If you still have the column B that I suggested earlier, then you can
do this in C1 of Sheet2:

=IF(B1="Duplicated",VLOOKUP(A1,Sheet1!A:C,3,0),"")

and copy down. If you don't have that column B, then you can do it
this way (still in C1):

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",VLOOKUP(A1,Sheet1!A:C,3,0))

and copy this down.

Both of them will return a value from column C of Sheet1 where the
name matches in column A, but will show a blank cell for non-
duplicated names.

Hope this helps.

Pete
 
Top