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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sharon" wrote in message
...
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

Is there a formula for this. I want to find the duplicates in two sets of
data?


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sharon" wrote in message
...
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

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","Duplica ted")

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","Duplica ted")

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

On Jan 30, 6:22*pm, Farmer Ted
wrote:
Is there a formula for this. *I want to find the duplicates in two sets of
data?



"Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sharon" wrote in message
...
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- Hide quoted text -

- Show quoted text -

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)?

"Pete_UK" wrote:

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","Duplica ted")

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","Duplica ted")

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

On Jan 30, 6:22 pm, Farmer Ted
wrote:
Is there a formula for this. I want to find the duplicates in two sets of
data?



"Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sharon" wrote in message
...
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- Hide quoted text -

- Show quoted text -

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,She et1!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

On Jan 30, 8:19*pm, Farmer Ted
wrote:
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)?



"Pete_UK" wrote:
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","Duplica ted")

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","Duplica ted")

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

On Jan 30, 6:22 pm, Farmer Ted
wrote:
Is there a formula for this. *I want to find the duplicates in two sets of
data?

"Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sharon" wrote in message
...
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -