Phone number totaling

[crgriggs]

Hello, I have 313 phone number (some are the same) that I would like to turn in to a percentage, for instance, 70% of calls = (334) 555-0001, 15% = (334) 555-0008, and so on. I have tried to get this to work for 45 minutes but I am currently stumped. Thanks

 

[dtchan]

Can't you just do a countif of the phone number and divide it by the total number of calls?

 

[crgriggs]

I'm not real sure, my excel knowledge is very limited. Sorry but that is why I came here, hoping for an answer.

 

[dtchan]

Here try this:
Select the entire column of your phone numbers, and run this code here.
let me know how it goes

Public Sub ReturnPercentage()

Dim rngToSearch As Range
Dim rngFiltered As Range
Dim rngCurrent As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

rngToSearch.AdvancedFilter xlFilterCopy, , rngToSearch.Offset(0, 2), True

Set rngFiltered = Range(rngToSearch.Offset(0, 2).End(xlUp), rngToSearch.Offset(0, 2).End(xlDown))


For Each rngCurrent In rngFiltered

If rngCurrent.Offset(1, 0).Value <> "" Then
rngCurrent.Offset(1, 1).Formula = "=Countif(" & rngToSearch.Address & "," & _
rngCurrent.Offset(1, 0).Address & ")/" & "(" & rngToSearch.Rows.Count - 1 & ")"

rngCurrent.Offset(1, 1).NumberFormat = "0.0%"
End If

Next rngCurrent

End Sub

 

[dtchan]

Actually use this code instead.
Just select any cell that has a phone number and run the code.


Public Sub ReturnPercentage()

Dim rngToSearch As Range
Dim rngFiltered As Range
Dim rngCurrent As Range

Set rngToSearch = Range(ActiveCell.EntireColumn.End(xlUp), ActiveCell.EntireColumn.End(xlDown))

Range(rngToSearch.Offset(0, 2), rngToSearch.Offset(0, 3)).Clear
rngToSearch.AdvancedFilter xlFilterCopy, , rngToSearch.Offset(0, 2), True

Set rngFiltered = Range(rngToSearch.Offset(0, 2).End(xlUp), rngToSearch.Offset(0, 2).End(xlDown))

For Each rngCurrent In rngFiltered

If rngCurrent.Offset(1, 0).Value <> "" Then
rngCurrent.Offset(1, 1).Formula = "=Countif(" & rngToSearch.Address & "," & _
rngCurrent.Offset(1, 0).Address & ")/" & "(" & rngToSearch.Rows.Count - 1 & ")"

rngCurrent.Offset(1, 1).NumberFormat = "0.0%"
End If

Next rngCurrent

End Sub

 

[crgriggs]

Thanks, this worked nicely.