Counting reoccuring numbers.

Hi,

As it states i need help counting reoccuring numbers, specifically phone numbers. My list consist of multiple phone numbers and i what i want to do is sort them by 25 each.

basically it looks like this (each of these are phone numbers 1 is ex 111-1111-1111 2 is another etc...)

1
2
2
2
3
4
5
5
6

So this would be a total of 6 numbers instead of 9. I cant even access my pivot tables for some reason!

Thank you if you need for clarification please let me know!

I cannot remove duplicate just for documentation purposes, and cannot provide a sample but ill give another example of what im working with

This is how it would look:

xxx-xxx-xxx1
xxx-xxx-xxx1
xxx-xxx-xxx2
xxx-xxx-xxx3
xxx-xxx-xxx4
xxx-xxx-xxx4

So im guessing a formula would be best? I want it to count the total numbers of this partciular list a 4 numbers not 6.

Which ever method is easier would help.

Thank you

Originally Posted by shg

Row\Col	A	B
1	4	A1: =SUMPRODUCT(1/COUNTIF(A3:A8, A3:A8))
2
3	xxx-xxx-xxx1
4	xxx-xxx-xxx1
5	xxx-xxx-xxx2
6	xxx-xxx-xxx3
7	xxx-xxx-xxx4
8	xxx-xxx-xxx4

It's not a friendly formula performance-wise; if you have 10,000 numbers, it has to do 100,000,000 comparisons.

Originally Posted by Tony Valko

Here's another one. It's longer but is more efficient over large data sets.

Data Range

	A	B	C
1	Phone	------	Count
2	111-111-1111		6
3	222-222-2222
4	222-222-2222
5	222-222-2222
6	333-333-3333
7	444-444-4444
8	555-555-5555
9	555-555-5555
10	666-666-6666

This array formula entered in C2:

=SUM(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A2:A10)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you guys!! Ill def be trying this out on Monday and will get back then!