counting phone numbers

[Glenn]

I have a column that contains phone numbers. Some of the phone numbers are
repeated in more than one row.

I know there is a way, which I can't remember at the moment, to count the
numbers such as this:

Phone # of times it appears
555-555-1212 1
555-555-1213 1
555-555-1212 2
555-555-1214 1

Can someone remind me how to do this, please?

Thanks.

 

[Dan E]

Glenn,

You appear to be counting numbers above the one that is currently
being considered (ie. 1212 counts 1 the first time then 2 the second)

=COUNTIF(A$1:A1,A1)

drag down

Dan E

 

[Glenn]

Maybe my example wasn't that good. I need to find the duplicates anywhere
in the entire list. It might be the record above it, or it could be 50 or
100 above it.

Thanks.

 

[Glenn]

What I am trying to is to make a mailing list based on the phone number, and
I don't want to send duplicates.

I know I've done it before, but can't remember how.

Thanks.

 

[Peo Sjoblom]

If you want to find the duplicates so you can remove them considering using
a filter instead
data>filter>advanced filter, select copy to another location and unique
record only.
If you want to see which are the duplicates, use countif

=COUNTIF($A$2:A2,A2)>1

copy down alongside the data, the entries with TRUE have duplicates

 

[Dan E]

But will it always above?

=COUNTIF(A$1:A1,A1)

A1 would be the first cell. As you drag this formula down it will look for
matching values in the entire list ABOVE the adjacent cell. ie. If you
have this formula in B1 and drag it down to B54 the formula in B54 will
be: =COUNTIF(A$1:A54, A54) . It will count all the values that match
A54 in the range A1:A54.

If you want to count values above and below use:
=COUNTIF(A$1:A$100, A1)
where A1:A100 is entire list

Dan E

 

[Glenn]

Thanks. That works. I had made an error when I put it in the first time,
and was a row off (I started with the A$1 when it should have been A$2).

Thanks for your help.
Glenn