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