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