List without repetitions

[mewingkitty]

I know there's like a million posts on here with this, I can't seem to find any, I'll keep looking in the meantime though.

Looking to list duplicates in a column without spaces.

I apologize because I'm sure this is a bazillionate post, I'll keep looking in the meantime.

mew...

[Ron Coderre]

For a list entered into D2:D20, with D1 as the column heading

These ARRAY FORMULAS must be committed with CTRL+SHIFT+ENTER,
instead of just ENTER:

For the first item...
D2: =INDEX(D2:D20,MATCH(0,-ISBLANK(D2:D20),0))

For all other list items...
D3: =IF(COUNT(MATCH(D$2:D$20,E$2:E2,0))<COUNT(1/(D$2:D$20<>"")),
INDEX(D$2:D$20,MATCH(0,(D$2:D$20<>"")-ISNA(MATCH(D$2:D$20,E$2:E2,0)),0)),"")

Copy D3 into D4 and down through as many cells as you need to cover all possible unique items

Is that something you can work with?

[mewingkitty]

Checking it now.
I know how to filter out duplicate results, I wanted to get my hands on a code that will accomplish that without having to delete the duplicate cells. I'll see how this works.

Thanks.

mew.

Edit:
Lists all individual values correctly, without duplicates. I already know how to isolate duplicates, so I should be able to combine the two and come out with a unique list of my duplicate list, thanks again.

and

mew.

again.

Edit:
Uploaded example worksheet for others reference. Note that all formulas in column AN are arrays, and that AN2 differs from the rest of AN, as given by Ron Coderre's example.

[MickG]

Hi, Try this:-
Results in column "A"

Dim Rng, Dup As Integer, Dup2 As Integer, c As Integer
Dim found As Boolean

Rng = Range(Range("d2"), Range("d" & Rows.Count).End(xlUp))
For Dup = 1 To UBound(Rng)
found = False
    For Dup2 = Dup + 1 To UBound(Rng)
        If Rng(Dup, 1) <> "" And Rng(Dup, 1) = Rng(Dup2, 1) Then
            If found = False Then
                c = c + 1
                Cells(c, "A") = Rng(Dup, 1)
                found = True
            End If
                Rng(Dup2, 1) = ""
        End If
    Next Dup2
Next Dup

Regards Mick