Identify duplicate values
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 |
Identify duplicate values
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 |
Identify duplicate values
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 |
All times are GMT +1. The time now is 06:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com