Count number of unique codes in a column

[rob51852]

Hi, I have a spreadsheet which contains a list of 70,000 numeric codes in column B. Some of the codes are duplicated multiple times.

Is there a formula that will count the number of unique codes?

Thanks

 

[James006]

Hi,

You can use following formula

=SUMPRODUCT(1/COUNTIF(yourRange,yourRange))

Hope this will help

 

[rob51852]

Hi,

You can use following formula

=SUMPRODUCT(1/COUNTIF(yourRange,yourRange))

Hope this will help

Hi James,

Thanks. The formula just returns a 0... Have I transposed it wrong:

=SUMPRODUCT(1/COUNTIF(D2:D61932,D2:D61932))

 

[James006]

Hi,

Apparently 70'000 records is too much for Excel to swallow ...

Would suggest to Insert a Pivot Table ... much safer ...

 

[rob51852]

Hi,

Apparently 70'000 records is too much for Excel to swallow ...

Would suggest to Insert a Pivot Table ... much safer ...

Thanks. Just came back to my PC and it's done it: 2152

Thanks again

 

[James006]

Glad you could fix your problem ...

 

[mumps]

Assuming you have headers in row 1, try this macro:

Sub CountUniques()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rowCount As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Range("B1:B" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("B1:B" & LastRow), Unique:=True
    rowCount = [subtotal(103,B:B)] - 1
    MsgBox ("There are " & rowCount & " unique values.")
    Range("B1").AutoFilter
    Application.ScreenUpdating = True
End Sub