Counting unique values

[Dean]

I am trying to count the number of unique values in my data. Here is
an example:

589450
589451
589452
589453
589454
589455
589455
589455
589455

Ideally I want to know that the count for the first 5 values will be
1, but also the count of 4 for the last four values, such as:

589450 1
589451 1
589452 1
589453 1
589454 1
589455 4

I have tried VLOOKUP, advanced filters, and COUNTIF but nothing has
worked properly and with some I get the following result:

589450 1
589451 1
589452 1
589453 1
589454 1
589455 4
589455 4
589455 4
589455 4

I have over 19,000 values that I need to count in this way so
duplicates as seen above in the last example are not desirable. Any
help would be greatly appreciated, and many thanks in advance for your
help.

 

[T. Valko]

Use the advanced filter and copy the uniques to another location. Then use a
COUNTIF formula.

Assume the raw data range is A1:A19000

The uniques have been filtered and copied to C1:C6.

Enter this formula in D1 and copy down to D6:

=COUNTIF(A$1:A$19000,C1)

 

[Dean]

This is perfect, thanks a lot. Can you explain the formula a bit more
so that I understand how to use it again in the future?

 

[T. Valko]

=COUNTIF(A$1:A$19000,C1)

Counts the number of cells within the range that meet the criteria.

In this case the range is A1:A19000 and the criteria is the value in C1.

See Excel help on COUNTIF.