Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurrences
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurrences
Hello,
Google for "pstat sulprobil". Regards, Bernd |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurrences
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" skrev i meddelelsen ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Occurrences
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of occurrences... | Excel Worksheet Functions | |||
counting occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences | Excel Discussion (Misc queries) | |||
Counting occurrences on a particular date | Excel Worksheet Functions |