Looking for duplicate data

  • Thread starter Thread starter gatarossi
  • Start date Start date
G

gatarossi

Dear all,

I need to run a VBA code, but before do it, I would like that excel
analyse the data ( I need to verify it in a range, for example
"A7:A65336" or/and "D6:IV6"): if there is some duplicate data in this
range, then:
msgbox "verify!!! there is duplicate date in the range", vb
information
or
run the VBA code...

Thanks a lot!!!

André.
 
You could use a formula like this in a cell in a worksheet:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

To count the number of unique entries in A1:A10.

So in code, you could use something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myFormula As String
Dim NumberOfUniqueEntries As Long

With Worksheets("sheet1")
Set myRng = .Range("a7", .Cells(.Rows.Count, "A").End(xlUp))
End With

myFormula = "sumproduct((" & myRng.Address(external:=True) & "<>"""")" _
& "/countif(" & myRng.Address(external:=True) & "," _
& myRng.Address(external:=True) &
"&""""))"

NumberOfUniqueEntries = Application.Evaluate(myFormula)

If NumberOfUniqueEntries = Application.CountA(myRng) Then
MsgBox "All Unique"
Else
MsgBox "Duplicates"
End If

End Sub
 
Watch out for line wrap (no other change except for that fix):

Option Explicit
Sub testme()

Dim myRng As Range
Dim myFormula As String
Dim NumberOfUniqueEntries As Long

With Worksheets("sheet1")
Set myRng = .Range("a7", .Cells(.Rows.Count, "A").End(xlUp))
Set myRng = .Range("D6:Iv6")
End With

myFormula = "sumproduct((" & myRng.Address(external:=True) & "<>"""")" _
& "/countif(" & myRng.Address(external:=True) & "," _
& myRng.Address(external:=True) & "&""""))"

NumberOfUniqueEntries = Application.Evaluate(myFormula)

If NumberOfUniqueEntries = Application.CountA(myRng) Then
MsgBox "All Unique"
Else
MsgBox "Duplicates"
End If

End Sub
 
Dear Dave,

Thanks a lot!!! It works!!!

but I'm thinking... I have a word (formula) that can be duplicated,
and only for this word, excel can acept like "all unique". There is a
form to adapt it in this second code?

Thanks a lot

André.
 
So you have some text that can be used multiple times that shouldn't be included
in the duplicate search?

Option Explicit
Sub testme()

Dim myRng As Range
Dim myFormula As String
Dim NumberOfUniqueEntries As Long
Dim myOkDupe As String
Dim NumberOfOkDupes As Long

myOkDupe = "DNA" 'like "does not apply

With Worksheets("sheet1")
Set myRng = .Range("a7", .Cells(.Rows.Count, "A").End(xlUp))
End With

myFormula = "sumproduct((" & myRng.Address(external:=True) & "<>"""")" _
& "/countif(" & myRng.Address(external:=True) & "," _
& myRng.Address(external:=True) & "&""""))"

NumberOfUniqueEntries = Application.Evaluate(myFormula)

NumberOfOkDupes = Application.CountIf(myRng, myOkDupe)
If NumberOfOkDupes > 0 Then
NumberOfUniqueEntries = NumberOfUniqueEntries - 1
End If

If NumberOfUniqueEntries + NumberOfOkDupes = Application.CountA(myRng) Then
MsgBox "All Unique"
Else
MsgBox "Duplicates"
End If

End Sub

Say you have:
xxx
dna
yyy
dna
zzz
dna

Then the number of uniques would be 4.
The number of dna's is 3. But since dna is counted in both the number of
uniques and the number of dna's, we subtract one. (The code subtracted 1 from
the numberofuniqueentries.)
 
Ps.

In one post, I had this:

With Worksheets("sheet1")
Set myRng = .Range("a7", .Cells(.Rows.Count, "A").End(xlUp))
Set myRng = .Range("D6:Iv6")
End With

You'll only want to use one range--I was just testing and forgot to delete the
second line.
 
Back
Top