Counting duplicates in Excel

Question

I have a list of postcodes that includes duplicates. I would like to find out how many instances of each postcode there are.

For example I would like this:

GL15 GL15 GL15 GL16 GL17 GL17 GL17 

...to become this:

GL15 3 GL15 3 GL15 3 GL16 1 GL17 2 GL17 2 

...or ideally this:

GL15 3 GL16 1 GL17 3 

Thanks!

Answer 1

I don't know if it's entirely possible to do your ideal pattern. But I found a way to do your first way: CountIF

+-------+-------------------+ |   A   |         B         | +-------+-------------------+ | GL15  | =COUNTIF(A:A, A1) | +-------+-------------------+ | GL15  | =COUNTIF(A:A, A2) | +-------+-------------------+ | GL15  | =COUNTIF(A:A, A3) | +-------+-------------------+ | GL16  | =COUNTIF(A:A, A4) | +-------+-------------------+ | GL17  | =COUNTIF(A:A, A5) | +-------+-------------------+ | GL17  | =COUNTIF(A:A, A6) | +-------+-------------------+