Return top 3 occurances of word in list

**Steel Monkey** · 07-05-2006, 09:20 PM

Good day to you all!
I have a problem and hopefully somone will be able to help me out with it

I have a list of names in Column D. Whenever someone generates a lead their name gets entered into this list. What I need to do is display the 3 names that appear most often i.e if bob generated 10 leads, rob generated 9 leads, tim generated 8 leads and sue generated 4 leads i would need: 1st Bob, 2nd Rob, 3rd Tim.

I have about 15 names but this changes all the time with different names being added.

I currently have a macro that displays the most common occurance of a name but i have no idea how to get it to display the other two.

Here is the code:

Sub maxcount()
Dim content As String
Dim mxcount As String
Dim mxuser As String
Dim mxusercount As Integer

Application.ScreenUpdating = False
Range("F1").Select
content = ActiveCell.Value
Selection.FormulaArray = _
"=INDEX(RC[-2]:R[9999]C[-2],MATCH(MAX(COUNTIF(RC[-2]:R[9999]C[-2],RC[-2]:R[9999]C[-2])),COUNTIF(RC[-2]:R[9999]C[-2],RC[-2]:R[9999]C[-2]),0))"
mxcount = ActiveCell.Value
Range("D1").Select
Do While ActiveCell.Value <> ""
If ActiveCell.Value = mxcount Then
mxusercount = mxusercount + 1
End If
ActiveCell.Offset(1, 0).Select
Loop

MsgBox "The most common user name is " & mxcount & " with " & mxusercount & " referrals."
Range("F1").Select
ActiveCell.Value = content
Application.ScreenUpdating = True
End Sub

I dont know if i am going about this totally wrong or what, but any help would be great!

Hope this makes sense

Stefi · 07-06-2006, 05:50 AM

Hi Steel Monkey,

Try this macro:
Sub FirstThree()
Range("D1") = "Name"
Range("E1") = "Frequency"
Range("H1") = "First 3 Freqs"
Range("I1") = "First 3 Names"
NoOfRows = ActiveSheet.Columns("D:D").Find("*", [D1], , , xlByRows,
xlPrevious).Row
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("E2:E" & NoOfRows),
Type:=xlFillDefault
Range("D1").Select
Range("D1:E" & NoOfRows).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"D1:D" & NoOfRows), CopyToRange:=Columns("F:G"), Unique:=True
noOfNames = ActiveSheet.Columns("F:F").Find("*", [F1], , , xlByRows,
xlPrevious).Row
Range("H2").Select
ActiveCell.Formula = "=LARGE(G$2:G$" & noOfNames & ",ROW()-1)"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(R2C[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],R2C[-2]:R" &
noOfNames & "C[-2],0))"
Range("H2:I2").Select
Selection.AutoFill Destination:=Range("H2:I4"), Type:=xlFillDefault
Range("I2:I4").Select
End Sub

Be aware of using columns E:I, if it conflicts with your other data, change
these columns!

You get the result in Range("I2:I4")!

Regards,
Stefi

â€žSteel Monkeyâ€ ezt Ã*rta:

>
> Good day to you all!
> I have a problem and hopefully somone will be able to help me out with
> it
>
> I have a list of names in Column D. Whenever someone generates a lead
> their name gets entered into this list. What I need to do is display
> the 3 names that appear most often i.e if bob generated 10 leads, rob
> generated 9 leads, tim generated 8 leads and sue generated 4 leads i
> would need: 1st Bob, 2nd Rob, 3rd Tim.
>
> I have about 15 names but this changes all the time with different
> names being added.
>
> I currently have a macro that displays the most common occurance of a
> name but i have no idea how to get it to display the other two.
>
> Here is the code:
>
> Sub maxcount()
> Dim content As String
> Dim mxcount As String
> Dim mxuser As String
> Dim mxusercount As Integer
>
>
>
> Application.ScreenUpdating = False
> Range("F1").Select
> content = ActiveCell.Value
> Selection.FormulaArray = _
>
> "=INDEX(RC[-2]:R[9999]C[-2],MATCH(MAX(COUNTIF(RC[-2]:R[9999]C[-2],RC[-2]:R[9999]C[-2])),COUNTIF(RC[-2]:R[9999]C[-2],RC[-2]:R[9999]C[-2]),0))"
> mxcount = ActiveCell.Value
> Range("D1").Select
> Do While ActiveCell.Value <> ""
> If ActiveCell.Value = mxcount Then
> mxusercount = mxusercount + 1
> End If
> ActiveCell.Offset(1, 0).Select
> Loop
>
> MsgBox "The most common user name is " & mxcount & " with " &
> mxusercount & " referrals."
> Range("F1").Select
> ActiveCell.Value = content
> Application.ScreenUpdating = True
> End Sub
>
> I dont know if i am going about this totally wrong or what, but any
> help would be great!
>
> Hope this makes sense
>
>
> --
> Steel Monkey
> ------------------------------------------------------------------------
> Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
> View this thread: http://www.excelforum.com/showthread...hreadid=558710
>
>

**Steel Monkey** · 07-06-2006, 07:29 PM

Hi Stefi

Thansk for your reply!

This works well..but if there are multiple users that have the same amount numbe rof occurances it doesnt quite work. For some reason number one occurance of a name gets repeated twice in the First 3 Names column ??
E.g. If i have this data:
Bill
Tom
Alan
Tom
Bill
Carl
Bill
Tom
Bill
Carl
Alice
Alice
Alice
Tom

The macro returns the following values:
First 3 Freqs First 3 Names
4 Bill
4 Bill
3 Alice

Any ideas ?
Thanks again for your reply :-)

Stefi · 07-07-2006, 04:35 AM

Hi Steel Monkey,
Try this new version:

Sub FirstThree()
Range("D1") = "Name"
Range("E1") = "Frequency"
Range("H1") = "First 3 Freqs"
Range("I1") = "First 3 Names"
NoOfRows = ActiveSheet.Columns("D:D").Find("*", [D1], , , xlByRows,
xlPrevious).Row
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("E2:E" & NoOfRows),
Type:=xlFillDefault
Range("D1").Select
Range("D1:E" & NoOfRows).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"D1:D" & NoOfRows), CopyToRange:=Columns("F:G"), Unique:=True
noOfNames = ActiveSheet.Columns("F:F").Find("*", [F1], , , xlByRows,
xlPrevious).Row
Range("H2").Select
ActiveCell.Formula = "=LARGE(G$2:G$" & noOfNames & ",ROW()-1)"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(RC[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],RC[-2]:R" &
noOfNames & "C[-2],0))"
Range("H2:I2").Select
Selection.AutoFill Destination:=Range("H2:I4"), Type:=xlFillDefault
Range("I2:I4").Select
End Sub

Regards,
Stefi

â€žSteel Monkeyâ€ ezt Ã*rta:

>
> Hi Stefi
>
> Thansk for your reply!
>
> This works well..but if there are multiple users that have the same
> amount numbe rof occurances it doesnt quite work. For some reason
> number one occurance of a name gets repeated twice in the First 3 Names
> column ??
> E.g. If i have this data:
> Bill
> Tom
> Alan
> Tom
> Bill
> Carl
> Bill
> Tom
> Bill
> Carl
> Alice
> Alice
> Alice
> Tom
>
> The macro returns the following values:
> First 3 Freqs First 3 Names
> 4 Bill
> 4 Bill
> 3 Alice
>
> Any ideas ?
> Thanks again for your reply :-)
>
>
> --
> Steel Monkey
> ------------------------------------------------------------------------
> Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
> View this thread: http://www.excelforum.com/showthread...hreadid=558710
>
>

Stefi · 07-11-2006, 01:35 PM

Sorry Steel Monkey, my previous solution is not complete, but this one is:

Sub FirstThree()
Range("D1") = "Name"
Range("E1") = "Frequency"
Range("H1") = "First 3 Freqs"
Range("I1") = "First 3 Names"
NoOfRows = ActiveSheet.Columns("D:D").Find("*", [D1], , , xlByRows,
xlPrevious).Row
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
Selection.AutoFill Destination:=Range("E2:E" & NoOfRows),
Type:=xlFillDefault
Range("D1").Select
Range("D1:E" & NoOfRows).AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=Range( _
"D1:D" & NoOfRows), CopyToRange:=Columns("F:G"), Unique:=True
noOfNames = ActiveSheet.Columns("F:F").Find("*", [F1], , , xlByRows,
xlPrevious).Row

Range("F1:G" & noOfNames).Select
Selection.Sort Key1:=Range("G2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Range("H2").Select
ActiveCell.Formula = "=LARGE(G$2:G$" & noOfNames & ",ROW()-1)"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=INDEX(RC[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],RC[-2]:R" &
noOfNames & "C[-2],0))"
Range("H2:I2").Select
Selection.AutoFill Destination:=Range("H2:I4"), Type:=xlFillDefault
Range("I2:I4").Select
End Sub

In fact the lines after Sort are not really necessary because the top three
names will be displayed as the first three items in column F.

Regards,
Stefi

â€žStefiâ€ ezt Ã*rta:

> Hi Steel Monkey,
> Try this new version:
>
> Sub FirstThree()
> Range("D1") = "Name"
> Range("E1") = "Frequency"
> Range("H1") = "First 3 Freqs"
> Range("I1") = "First 3 Names"
> NoOfRows = ActiveSheet.Columns("D:D").Find("*", [D1], , , xlByRows,
> xlPrevious).Row
> Range("E2").Select
> ActiveCell.FormulaR1C1 = "=COUNTIF(C[-1],RC[-1])"
> Selection.AutoFill Destination:=Range("E2:E" & NoOfRows),
> Type:=xlFillDefault
> Range("D1").Select
> Range("D1:E" & NoOfRows).AdvancedFilter Action:=xlFilterCopy,
> CriteriaRange:=Range( _
> "D1:D" & NoOfRows), CopyToRange:=Columns("F:G"), Unique:=True
> noOfNames = ActiveSheet.Columns("F:F").Find("*", [F1], , , xlByRows,
> xlPrevious).Row
> Range("H2").Select
> ActiveCell.Formula = "=LARGE(G$2:G$" & noOfNames & ",ROW()-1)"
> Range("I2").Select
> ActiveCell.FormulaR1C1 = _
> "=INDEX(RC[-3]:R" & noOfNames & "C[-3],MATCH(RC[-1],RC[-2]:R" &
> noOfNames & "C[-2],0))"
> Range("H2:I2").Select
> Selection.AutoFill Destination:=Range("H2:I4"), Type:=xlFillDefault
> Range("I2:I4").Select
> End Sub
>
> Regards,
> Stefi
>
> â€žSteel Monkeyâ€ ezt Ã*rta:
>
> >
> > Hi Stefi
> >
> > Thansk for your reply!
> >
> > This works well..but if there are multiple users that have the same
> > amount numbe rof occurances it doesnt quite work. For some reason
> > number one occurance of a name gets repeated twice in the First 3 Names
> > column ??
> > E.g. If i have this data:
> > Bill
> > Tom
> > Alan
> > Tom
> > Bill
> > Carl
> > Bill
> > Tom
> > Bill
> > Carl
> > Alice
> > Alice
> > Alice
> > Tom
> >
> > The macro returns the following values:
> > First 3 Freqs First 3 Names
> > 4 Bill
> > 4 Bill
> > 3 Alice
> >
> > Any ideas ?
> > Thanks again for your reply :-)
> >
> >
> > --
> > Steel Monkey
> > ------------------------------------------------------------------------
> > Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
> > View this thread: http://www.excelforum.com/showthread...hreadid=558710
> >
> >

**Steel Monkey** · 07-16-2006, 11:26 PM

Thanks Stefi

Works like a charm :-)

Stefi · 07-17-2006, 02:45 AM

You are welcome! Thanks for the feedback!
Stefi

â€žSteel Monkeyâ€ ezt Ã*rta:

>
> Thanks Stefi
>
> Works like a charm :-)
>
>
> --
> Steel Monkey
> ------------------------------------------------------------------------
> Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051
> View this thread: http://www.excelforum.com/showthread...hreadid=558710
>
>