Counting Occurrences

[JH]

Hello,

For starters, I am using Excel 07.

I have a list of phone numbers which are all in one column. I am trying to
count the number of times each phone number appears on this list. I would
usually just sort and manually count, however, the current list is over 3,500
rows long so that is not an option. Could someone please advise me of how to
accomplish this?

Also, I am aware that this can be done with pivot tables but I need to be
able to modify the list once I have the number of occurrences.

TIA

 

[Bernd P]

Hello,

Google for "pstat sulprobil".

Regards,
Bernd

 

[Per Jessen]

Hi

First create a list of unique numbers. Select the list, goto Data > Filter >
Advanced filter > Select: Copy to another location > Check "Unique records
only" > Copy to: Select a single cell where you want to place the unique
list > OK

Suppose the original list are in column A and Unique numbers i colum B, Use
this formula in colmn C:

=countif(A2:A100=B2) (headings in row 1)

Copy the formula down as required.

Hopes this helps.
....
Per

 

[john]

this may do what you want - paste in standard module.

Sub CountNumbers()
'extract list
With Sheets("Sheet1")
'assume phone numbers in col A
'change as required
'Also assume col J empty - change as required
.Columns("A:A").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Range("J1"), Unique:=True

lr = .Cells(.Rows.Count, "J").End(xlUp).Row

'add heading
.Range("K1").Value = "Count"

'assume your col has a heading
'start from row 2
For na = 2 To lr

.Cells(na, 11).Formula = "=COUNTIF(A:A,J" & na & ")"

Next

End With
End Sub