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

Hello,

Google for "pstat sulprobil".

Regards,
Bernd

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

"JH" <(E-Mail Removed)> skrev i meddelelsen
news:2D07DFAF-B53C-4DFA-8E04-(E-Mail Removed)...
> 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

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
--
jb


"JH" wrote:

> 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