CODEWITHSUNDEEP
excelvba
OneFineDay
OneFineDay

Reputation: 9024

Get a list of all duplicates in a column

I need to get all the duplicates in a column. The column is currently sorted. I believe this will need to be a macro(maybe a function) so it can add the duplicate values to another column. I want to paste a range of cells in column A and have column B give me the duplicates. Example below.

Column A

SD1000023

SD1000024

SD1000024

SD1000025

SD1000026

SD1000026

SD1000027

Then my column would have:

Column B

SD1000024

SD1000026

From here I found this, just not sure how to fix it for my needs. I don't understand the list1 part.

=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0))

Upvotes: 0

Views: 396

Answers (4)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

In the column next to it, you can use the formula =COUNTIF(A:A,A2) and filter on anything not equal to 1

Upvotes: 0

findwindow
findwindow

Reputation: 3153

Conditional format column A for duplicates. This macro will then label all duplicates in column B.

Sub dup()

Dim lastRow As Long
lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow
If Cells(i, 1).DisplayFormat.Interior.Color <> 16777215 Then
Cells(i, 2) = "Dup"
End If
Next

End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

You can use the "Remove Duplicates" application in the Data Tab. First, Copy Column A, paste into Column B. Then choose Data -> Remove Duplicates and select Column B (don't extend to Column A when asked). Then click "Ok", this will leave you with a list of unique values. Then, of those, to see which have duplicates, you could (in column C), do "=Countif(B1,A:A)" to see how many times that value occurs in the list.

Here's a macro that should get you going. Note, I assume that you have a header row, if not you just need to tweak slightly:

Sub List_Duplicates()
Dim lastRow As Integer, dataCol As Integer, duplicateCol As Integer, lastUniqueRow As Integer
Dim dataRng As Range, dupRng As Range
Dim ws            As Worksheet
Set ws = ActiveSheet
With ws
    lastRow = .Cells(1, 1).End(xlDown).Row    'This assumes your Column A has no line breaks, if it does, comment this out and use below
    'lastRow = .UsedRange.Rows.Count
    dataCol = 1              'if your list of data, including duplicates, is in COlumn A
    duplicateCol = 2         'where to put the duplicate number

    Set dataRng = .Range(.Cells(2, dataCol), .Cells(lastRow, dataCol))
    Set dupRng = .Range(.Cells(2, duplicateCol), .Cells(lastRow, duplicateCol))

    'First, we will copy the Column A data to column B
    dupRng.Value = dataRng.Value

    'Now, remove duplicates from this

    lastUniqueRow = .Cells(2, duplicateCol).End(xlDown).Row
    dupRng.RemoveDuplicates Columns:=1, Header:=xlNo
    'Evaluate the countif formula, and then leave duplicates in col. B
    Dim i         As Integer
    For i = 2 To lastUniqueRow 'This will loop through our duplicate cells, and remove any that are not duplicates
        If .Cells(i, duplicateCol).Row > lastUniqueRow Then Exit For
        Debug.Print .Cells(i, duplicateCol).Value & " occurrs " & Evaluate(WorksheetFunction.CountIf(dataRng, .Cells(i, duplicateCol))) & " times."
        If Evaluate(WorksheetFunction.CountIf(dataRng, .Cells(i, duplicateCol))) <= 1 Then
            .Cells(i, duplicateCol).Value = ""
        End If
    Next i
    dupRng.Select
    dupRng.SpecialCells(xlCellTypeBlanks).Delete
End With
End Sub

Let me know if you have any questions! And if anyone else has any comments or suggestions for the above, kindly let me know so I can learn too :P

Upvotes: 2

John Coleman
John Coleman

Reputation: 51988

This might help:

Assuming that the data starts in A1, In B1 enter

=IF(A1 = A2, A1,"")

And in B2 enter

=IF(AND(A2<>A1,A2 = A3),A2,"")

And copy this formula the rest of the way down B. This will cause the duplicated values to appear in column B exactly once. You can then copy them and paste them back in place as values, and then sort the result to get what you want gathered into adjacent cells.

Upvotes: 0

Related Questions