Return top 3 occurances of word in list

S

Steel Monkey

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
 
S

Stefi

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
Click to expand...
 
S

Steel Monkey

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 :)
 
S

Stefi

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:
 
S

Stefi

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 :)
Click to expand...
Click to expand...
 
S

Stefi

You are welcome! Thanks for the feedback!
Stefi


„Steel Monkey†ezt írta:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Index formula 2
Looping(Rookies) 4
Is it Possible to have a dynamic function in an array written in visual basic? 6
workbook open in looping 2
Compare 2 excel sheets -Error Checking of entered names 1
Help with my VBA and formula 8
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3
How to Insert Variables into Formula's 4

Top