| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return top 3 occurances of word in list
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
Thanks Stefi Works like a charm :- -- Steel Monke ----------------------------------------------------------------------- Steel Monkey's Profile: http://www.excelforum.com/member.php...fo&userid=2905 View this thread: http://www.excelforum.com/showthread.php?threadid=55871 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Return top 3 occurances of word in list
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number of occurances of a word | Excel Worksheet Functions | |||
| count the dates for each criteria and list total occurances for ea | Excel Worksheet Functions | |||
| count number of occurances of a word in a range | Excel Worksheet Functions | |||
| need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
| Return MS Word word counts in Excel? | Excel Programming | |||
![Steel Monkey[_8_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
