Macro to Copy duplicate values from a column to a separate sheet

Newbie here. In a sheet named 'raw data', I have this columnn (AA) with text values. I want a macro to first determine the duplicate values in this column and copy on these duplicate column values (not cut/remove) to a separate sheet 'analysis' in the workbook. I can't figure out how to do it.

Below is an illustrative example. So I want the muliple x's in column AA to be copied to a new sheet.

Any ideas?
Untitled.png

this gives sorted result

Sub dupes()
Dim a, c As Long, i As Long, k As Long

With Sheets("analysis").Range("A:A")
Sheets("raw data").Range("AA:AA").Copy .Cells(1)
    .Sort .Cells(1), 1, Header:=xlYes
    a = Range(.Cells(1), .Cells(1).End(4)(2))
    c = 1
    For i = 2 To UBound(a)
        If a(i, 1) <> a(c, 1) Then
            k = k + 1
            a(k, 1) = a(c, 1)
            c = i
        End If
    Next i
    .ClearContents
    .Resize(k) = a
End With

End Sub

Excel 2007 et seq also has a Remove duplicates facility if you want to risk using that.

Hi Kalak,

Thanks for this. If I understand correctly, does this procedure copy all the unique items from the column in a sorted manner?

Not quite.

It copies all items to the analysis sheet, then sorts them, then eliminates the duplicates.

If you want it done as you ask, can do it that way with advanced filter, which is OK if not too much data, or several other ways.

ah ok.
yi
well what i wanted was a list of items that have duplicates. this is a complaints analysis that i am trying to kind of automate and wanted to have a macro that would take a list of entities from the column and list out the ones which had the highest frequency of complaints in a period of time.

do you mean like this?

Sub xx()
Dim a As Object, b, c, rw As Long

Set a = CreateObject("scripting.dictionary")
a.comparemode = vbTextCompare
Sheets("raw data").Activate
rw = Cells(Rows.Count, "aa").End(3).Row
b = Cells(1, "aa").Resize(rw)
For c = 2 To rw
    a(b(c, 1)) = a(b(c, 1)) + 1
Next c

With Sheets("analysis")
    .Cells(2, 1).Resize(a.Count, 2) = _
        Application.Transpose(Array(a.keys, a.items))
    .Cells(1, 1) = b(1, 1)
    .Cells(1, 2) = "count"
End With
End Sub

that is exactly it!! thanks so much!!

i was thinking of tweaking this and was wondering if it is possible to show in the analysis sheet, only the ones which have a count of 5 or more in the list?

sorry i am getting greedy but your code has been really really helpful.

like this?

Sub xxx()
Dim a As Object, b, c, rw As Long

Set a = CreateObject("scripting.dictionary")
a.comparemode = vbTextCompare
Sheets("raw data").Activate
rw = Cells(Rows.Count, "aa").End(3).Row
b = Cells(1, "aa").Resize(rw)
For c = 2 To rw
    a(b(c, 1)) = a(b(c, 1)) + 1
Next c

For Each c In a.keys
    If a(c) < 5 Then a.Remove c
Next c

If a.Count > 0 Then
    With Sheets("analysis")
        .Cells(2, 1).Resize(a.Count, 2) = _
            Application.Transpose(Array(a.keys, a.items))
        .Cells(1, 1) = b(1, 1)
        .Cells(1, 2) = "count if>=5"
    End With
Else
    MsgBox "None are >=5"
End If

End Sub