CODEWITHSUNDEEP
sortingexcelvba
franklin
franklin

Reputation: 1819

suggestions for a macro to find duplicates in a SINGLE column

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?

Upvotes: 4

Views: 21266

Answers (7)

Neil.Corbin
Neil.Corbin

Reputation: 23

For a function returning a boolean try...

Option Explicit
Public Function DUPLICATE_VALUE(rngMyRange As Range, rngMyCell As Range) As Boolean

If WorksheetFunction.CountIf(rngMyRange, rngMyCell.Value) > 1 Then
    DUPLICATE_VALUE = True
Else:
    DUPLICATE_VALUE = False
End If

End Function

Upvotes: 0

markblandford
markblandford

Reputation: 3193

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.

Upvotes: 3

Micah
Micah

Reputation: 1

Building on Siddharth's response, if you want to highlight all instances of duplicates other than the first (to make it easy to simply select all that show up and eliminate them), you could use this modification of his string:

=IF(COUNTIF(A$1:A2,A2)>1,"D","S").

For conditional formatting, it would be like

=COUNTIF(A$1:A2,A2)>1.

This checks only the rows above the current cell, so the first instance of a duplicate will not be highlighted (as it won't have any duplicates above it).

Upvotes: 0

rkmax
rkmax

Reputation: 18125

This Works for me

Excel 2007

Sub removeDuplicate(rg As Range, col as Integer)
    rg.RemoveDuplicates Columns:=col, Header:=xlYes
End Sub

Excel 2003

' Excel 2003
Option Explicit

Sub DeleteDups(range as String) 

    Dim x               As Long 
    Dim LastRow         As Long 

    ' Range "A65536" 
    LastRow = Range(range).End(xlUp).Row 
    For x = LastRow To 1 Step -1 
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then 
            Range("A" & x).EntireRow.Delete 
        End If 
    Next x 

End Sub

Upvotes: 2

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

You can add

Option Compare Text

To the VERY TOP of the module, all the code in that module will now compare text non-case-sensitively.

CAT cat CaT cAt

...would all match.

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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

enter image description here Sid

Upvotes: 6

assylias
assylias

Reputation: 328598

You could put all the keys in lower case, for example:

Dim myKey as String

For i = lastRow To 1 Step -1
    myKey = UCase(range(Col & i).value)
    If dict.Exists(myKey) = True Then

    'range("Y" & i).EntireRow.Delete

    MsgBox ("Hmm...Seems to be a duplicate of " & range(Col & i).value & _
    " in Cell " & Col & i)

    Else
        dict.Add myKey, 1
    End If
Next i

Upvotes: 2

Related Questions