Find duplicates in a column

0 votes

The code below counts the duplicates in a particular column and displays the number of duplicates, but I need it to state "Duplicate found" in a subsequent cell if there is/are any duplicates. For instance, if values are the same in cells F3, F4, and F15. Blank column (column "G" is already present because I am verifying column "F"), then it should be sorted, and "Duplicate discovered" should appear in cells G3, G4, and G15.

Dim helperCol As Range
Dim count As Long

With Worksheets("Sheet1")
    Set helperCol = .UsedRange.Resize(, 1).Offset(, .UsedRange.Columns.count)
    With .Range("F1", .Cells(.Rows.count, 6).End(xlUp))
        helperCol.Value = .Value
        helperCol.RemoveDuplicates Columns:=1, Header:=xlYes
        count = .SpecialCells(xlCellTypeConstants).count - helperCol.SpecialCells(xlCellTypeConstants).count
    End With
    helperCol.ClearContents
End With

If count >= 1 Then
    Range(count, "G") =   " Duplicate/s found"
End If

enter image description here

the output should look like this:- (Bold font is done by me only just for clear understanding its not required )

enter image description here

Nov 3, 2022 in Others by Kithuzzz
• 38,010 points
604 views

1 answer to this question.

0 votes

If any cells in column "F" contain duplicates, this code will display "Duplicate Found" in the cell 1 across to the right (i.e., column "G") of those cells.

Option Explicit

Sub Test()

    Dim CEL As Range, RANG As Range

    With Worksheets("Sheet1")

        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))

    End With

    ' For each cell (CEL) in this range (RANG)
    For Each CEL In RANG

        ' If the count of CEL in RANG is greater than 1, then set the value of the cell 1 across to the right of CEL (i.e. column G) as "Duplicate Found"
        If Application.WorksheetFunction.CountIf(RANG, CEL.Value) > 1 Then CEL.Offset(, 1).Value = "Duplicate Found"

    Next CEL

End Sub

Another choice is to utilise a dictionary, which keeps track of distinct values and their ranges (first add a reference to Microsoft Scripting Runtime). As you work your way down the range, you fill in the Dictionary. If a value is already there, you should note "Duplicate found" for the initial range and all subsequent occurrences.

Tools > References

enter image description here

Sub Test2()

    Dim CEL As Range, RANG As Range
    Dim dict As New Scripting.Dictionary

    With Worksheets("Sheet1")

        ' Build a range (RANG) between cell F2 and the last cell in column F
        Set RANG = Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))

    End With

    ' For each cell (CEL) in this range (RANG)
    For Each CEL In RANG

        If CEL.Value <> "" Then ' ignore blank cells

            If Not dict.Exists(CEL.Value) Then ' if the value hasn't been seen yet
                dict.Add CEL.Value, CEL ' add the value and first-occurrence-of-value-cell to the dictionary
            Else ' if the value has already been seen
                CEL.Offset(, 1).Value = "Duplicate Found" ' set the value of the cell 1 across to the right of CEL (i.e. column G) as "Duplicate Found"
                dict(CEL.Value).Offset(, 1).Value = "Duplicate Found" ' set the value of the cell 1 across to the right of first-occurrence-of-value-cell (i.e. column G) as "Duplicate Found"
            End If

        End If

    Next CEL

    Set dict = Nothing

End Sub

I hope this helps you. 

answered Nov 4, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to find the last row in a column using openpyxl normal workbook?

ws.max_row will give you the number of rows ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
4,877 views
0 votes
1 answer

Find value in column where running total is equal to a certain percentage

VBA bubble sort - no changes to ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,420 points
204 views
0 votes
1 answer

Google Spreadsheet/ Excel - how to find matching values in column A, having necessary values in column M

Try this: =COUNTIF($M$2:$M$5;A2) If the product is present in ...READ MORE

answered Feb 10, 2023 in Others by narikkadan
• 63,420 points
233 views
0 votes
1 answer

Find specific term in a text string and return that term in the previous column

Try: You can either hard code the ...READ MORE

answered Apr 10, 2023 in Others by Kithuzzz
• 38,010 points
276 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,182 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

Is there a function in excel to find duplicates

Solution: You can use Conditional formatting inside the ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,420 points
409 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,886 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP