Worksheet duplicates

[puiuluipui]

Hi, i need to find duplicates in all sheets and result to be, "yes" or "no".
In "C" column i have numbers, and in "D" column i need the code for
duplicates.
If any number from "C" column is find in any sheet, the code to display in
"D", "yes" or "no".

Ex
C D
123 yes
321 no

Can this be done?
Thanks!

 

[Jacob Skaria]

Suppose you have the same numbers in Sheet2 Col C and Sheet 3 ColC.
And from Sheet1 you need find whether there is a duplicate; try the below

Suppose in Sheet1
C1 = 123
D1 = IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)>0,"Yes","No")

If this post helps click Yes

 

[Mike H]

Hi,

You'll have to be more specific, a worksheet (depending on version) has
around 16777216 cells so with (say) 3 worksheets that's more than 50 million
cells which is a demanding search to say the least.

Mike

 

[Don Guillett]

Sub findem()
For Each c In Range("c2:c14")
For Each ws In Worksheets
If ws.Name <> "Sheet4" Then
Set fc = ws.Cells.Find(c)
If fc Is Nothing Then c.Offset(, 1) = "Yes"
End If
Next ws
Next c
End Sub

 

[puiuluipui]

It's working, but if i don't have any number in ..let's say "C25", the code
display "no". if there is no number in a cell in "C" column, then the code to
display nothing. The cell to be empty.
Can this be done?
Thanks allot!



"Jacob Skaria" a scris:

 

[Don Guillett]

Better in that it is looking for whole numbers

Sub findwholenumbersinworkbook()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For Each c In Range("a1:a" & lr)
For Each ws In Worksheets
If ws.Name <> "Sheet3" Then
Set fc = ws.Cells.Find(c, lookat:=xlWhole)
If Not fc Is Nothing Then c.Offset(, 1) = "Yes"
End If
Next ws
Next c
End Sub

 

[Jacob Skaria]

=IF(C1="","",IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)>0,"Yes","No"))

If this post helps click Yes

 

[puiuluipui]

It's working! Beautiful!
Thanks allot!

=IF(C1="","",IF(COUNTIF(Sheet2!C:C,C1)+COUNTIF(Sheet3!C:C,C1)>0,"Yes","No"))

If this post helps click Yes

 

[puiuluipui]

Hi, i need a little more help. Your code is searching for the duplicates in
all sheets, except "sheet1". I need the code to search in "sheet1" except
cell with value, that need to be found ("C1").

Can this be done?
Thanks!

"Jacob Skaria" a scris: