How many times a number shows up

[thepartydj]

I have a list of 27,000 phone numbers in column A. There are about 500 unique numbers in this list. I need to find out how many times each number shows up in the list. How can I find that out? Thanks!

 

[Trevor G]

What about using count if

Excel Workbook
	A	B
1	Tel No	countif
2	tad 1234	1
3	tad 123245	1
4	dev 12	3
5	dev 12	3
6	dev 12	3
Sheet1

 

[T. Valko]

I have a list of 27,000 phone numbers in column A. There are about 500 unique numbers in this list. I need to find out how many times each number shows up in the list. How can I find that out? Thanks!

If you have the unique numbers listed in say C1:C500, enter this formula in D1:

=COUNTIF(A:A,C1)

Copy down to D500.

You can easily generate the list of unique names using an advanced filter:

http://contextures.com/xladvfilter01.html#FilterUR

 

[Yahya]

Try this code
the data before run the code
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Telphone Num</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1234</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1234</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12111</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12345</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">456</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">456</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">98675</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">456</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">123456</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After Run This code

Sub CountUnique()
Dim LR As Long, Rng As Range, c As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Range("A2:A" & LR)

Range("A1:A" & LR).AdvancedFilter xlFilterCopy, , [C1], True
For Each c In Range("C2:C" & Range("c" & Rows.Count).End(xlUp).Row)
    c.Offset(, 1).Value = WorksheetFunction.CountIf(Rng, c.Value)
Next c
End Sub

the result
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Telphone Num</td><td style="text-align: right;;"></td><td style=";">Telphone Num</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1234</td><td style="text-align: right;;"></td><td style="text-align: right;;">1234</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">1234</td><td style="text-align: right;;"></td><td style="text-align: right;;">12111</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">12111</td><td style="text-align: right;;"></td><td style="text-align: right;;">12345</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">12345</td><td style="text-align: right;;"></td><td style="text-align: right;;">456</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">456</td><td style="text-align: right;;"></td><td style="text-align: right;;">98675</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">456</td><td style="text-align: right;;"></td><td style="text-align: right;;">123456</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">98675</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">456</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">123456</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />