I am trying to determine if something can be done. Basically, I have two
spreadsheets and I need to identify duplicate values from the two sheets. If
a unique PIN on sheet A is also found on sheet B, I want to put a "1" in a
column beside that PIN on sheet A so that I can easily eliminate it. I've
done a lot of simple IF statements comparing values in adjacent rows but in
this case, for each PIN on sheet A, it will need to search the entire PIN
column on sheet B, and I can't think how to do this. It's something I would
normally do with a database, but that's not an option here. If someone can
point me in the right direction, I would greatly appreciate it.

Thanks,
Lee

How about:


Code
-------------------
Sub test()

Dim r1 As Range
Dim r2 As Range

For Each r1 In Sheets("SheetA").Range("A1:A1000")
For Each r2 In Sheets("SheetB").Range("A1:A1000")
If r1.Value = r2.Value Then
r1.Offset(0, 1).Value = 1
Exit For
End If
Next
Next

End Su
-------------------


Substituting your sheet names and the ranges for your PINs.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26895

COUNTIF should be able to do it. Assuming the PIN is in column A for both
sheets, then you could put a formula in another column on Sheet A to count if
that PIN appears on sheet B, and if so how many times. So, for example, on
row 1 use this formula where you want your "flag" to go:
=IF(COUNTIF('Sheet B'!A:A,A1)0,1,"")

K Dales

"LA Roper" wrote:

I am trying to determine if something can be done. Basically, I have two
spreadsheets and I need to identify duplicate values from the two sheets. If
a unique PIN on sheet A is also found on sheet B, I want to put a "1" in a
column beside that PIN on sheet A so that I can easily eliminate it. I've
done a lot of simple IF statements comparing values in adjacent rows but in
this case, for each PIN on sheet A, it will need to search the entire PIN
column on sheet B, and I can't think how to do this. It's something I would
normally do with a database, but that's not an option here. If someone can
point me in the right direction, I would greatly appreciate it.

Thanks,
Lee