found a lot of questions involving finding duplicates in two columns :
i.e. MS Excel how to create a macro to find duplicates and highlight them? and excel mark duplicates values
However I'm trying to adapt code to be used to find duplicates in one column. For example here is a data set:
Column 1
Foo
Bar
23
23
12
foo
Bar
bart
This is what I'm using right now:
Function warnDupes()
Dim lastRow As Long
Dim dict As Object
' Let Col be the column which warnDupes operates on.
Dim Col As String
Col = "A"
Set dict = CreateObject("scripting.dictionary")
lastRow = range(Col & Rows.Count).End(xlUp).Row
On Error Resume Next
For i = lastRow To 1 Step -1
If dict.Exists(range(Col & i).value) = True Then
'range("Y" & i).EntireRow.Delete
MsgBox ("Hmm...Seems to be a duplicate of " & range(Col & i).value & _
" in Cell " & Col & i)
End If
dict.Add range(Col & i).value, 1
Next
End Function
So far I've got some code that does 90% of the job. 23 and 23 are matched. Bar and Bar are matched. etc. So the code matches both Strings and Ints. But I'd like the macro to be able to match Foo and foo as a duplicate as well. How do I make Excel ignore case?
This question ( Function for detecting duplicates in Excel sheet ) seems relevent but I'm having trouble adapting the code or understanding what the author did. Any improvements to the code, explanations or suggestions would be very much appreciated.
Thanks.
UPDATE:
Just noticed something really weird.
The data:
IB6061
IC6071
are matched whether I use my Macro or if I use the Conditional Formatting tool in Excel. Any reason why?
franklin
Why not an Excel formula?
If the values are in Col A then type this in Cell B1 and copy it down?
=IF(COUNTIF(A:A,A1)>1,"It is a duplicate","It is not a duplicate")
It will also work for cases like "Foo" and "foo"
You can then also use Conditional Formatting using the above formula to highlight duplicates?
FOLLOWUP
The data:
IB6061
IC6071
are matched whether I use my Macro or if I use the Conditional Formatting tool in Excel.
Any reason why?
What formula are you using?
This works for me. Highlight Col A and then use this formula
=COUNTIF(A:A,A1)>1
See snapshot
Sid
On your Exists() & .Add() lines, make both values the same case:
If dict.Exists(UCase$(Range(Col & i).Value)) Then
and
dict.Add UCase$(Range(Col & i).Value), 1
That way the duplicates will always be added to the dictionary in uppercase and so case will never matter.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With