Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count array for top three entries and return their number
I am trying to solve a scoring problem for a car show.
In a spread sheet we will have entry number of cars running across columns that represent individual 1st place choices. Without filtering and resorting the data (no time at the car show) I need to dynamically determine the top three entires and return the number of times they show up. I.E if these are the car entry numbers in their respective columns. 1 2 1 3 2 4 2 4 1 1 1 4 2 Should return 1 5 2 4 4 3 I don't even need the second number (number of votes, but we probably need it to verify the formula is working). Thanks!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count array for top three entries and return their number
Rich
I assumed you have only one row of data and it's in row 1 starting in A1. This macro will produce a message box telling you the winner and how many votes for that winner. Post back if you need more or if my assumptions are wrong. HTH Otto Sub GetBest() Dim rRngRow1 As Range, i As Range Dim HowMany As Long, What As Long HowMany = 0 Set rRngRow1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft)) For Each i In rRngRow1 If Application.CountIf(rRngRow1, i.Value) HowMany Then HowMany = Application.CountIf(rRngRow1, i.Value) What = i.Value End If Next i MsgBox "Car number: " & What & Chr(13) & _ "How many: " & HowMany End Sub "Rich DeVito" wrote in message ... I am trying to solve a scoring problem for a car show. In a spread sheet we will have entry number of cars running across columns that represent individual 1st place choices. Without filtering and resorting the data (no time at the car show) I need to dynamically determine the top three entires and return the number of times they show up. I.E if these are the car entry numbers in their respective columns. 1 2 1 3 2 4 2 4 1 1 1 4 2 Should return 1 5 2 4 4 3 I don't even need the second number (number of votes, but we probably need it to verify the formula is working). Thanks!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count array for top three entries and return their number
Rich
If you have multiple rows of such voting, use the following macro. The results will be put in each row after the vote numbers and those 2 cells will be colored yellow. HTH Otto Sub GetBest2() 'If multiple rows Dim rRngRow As Range, i As Range, Dest As Range Dim HowMany As Long, What As Long Dim rColA As Range, j As Range Set rColA = Range("A1", Range("A" & Rows.Count).End(xlUp)) For Each j In rColA Set rRngRow = Range(j, Cells(j.Row, Columns.Count).End(xlToLeft)) Set Dest = rRngRow(rRngRow.Count).Offset(, 1) HowMany = 0 For Each i In rRngRow If Application.CountIf(rRngRow, i.Value) HowMany Then HowMany = Application.CountIf(rRngRow, i.Value) What = i.Value End If Next i Dest = What Dest.Offset(, 1) = HowMany Dest.Resize(, 2).Interior.ColorIndex = 6 Next j End Sub "Rich DeVito" wrote in message ... I am trying to solve a scoring problem for a car show. In a spread sheet we will have entry number of cars running across columns that represent individual 1st place choices. Without filtering and resorting the data (no time at the car show) I need to dynamically determine the top three entires and return the number of times they show up. I.E if these are the car entry numbers in their respective columns. 1 2 1 3 2 4 2 4 1 1 1 4 2 Should return 1 5 2 4 4 3 I don't even need the second number (number of votes, but we probably need it to verify the formula is working). Thanks!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count array for top three entries and return their number
Try this...
Assume your numbers are in the range A1:M1. In the formulas rng refers to A$1:M$1 Enter this formula in A5: =MODE(rng) Enter this array formula** in A6 and copy down to A7: =MODE(IF(COUNTIF(A$5:A5,rng)=0,rng+{0;0})) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Enter this formula in B5 and copy down to B7: =COUNTIF(rng,A5) -- Biff Microsoft Excel MVP "Rich DeVito" wrote in message ... I am trying to solve a scoring problem for a car show. In a spread sheet we will have entry number of cars running across columns that represent individual 1st place choices. Without filtering and resorting the data (no time at the car show) I need to dynamically determine the top three entires and return the number of times they show up. I.E if these are the car entry numbers in their respective columns. 1 2 1 3 2 4 2 4 1 1 1 4 2 Should return 1 5 2 4 4 3 I don't even need the second number (number of votes, but we probably need it to verify the formula is working). Thanks!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count array for top three entries and return their number
Thanks Otto!
I will try this. Now let me confuse the situation...I need the 1st, 2nd and 3rd place winners. Sorry, I forgot to mention this on the first pass question. I will try this and see if I can get it to work. The other solution was graphical using a graphical function list a distribution bar code. Thank you! "Otto Moehrbach" wrote: Rich I assumed you have only one row of data and it's in row 1 starting in A1. This macro will produce a message box telling you the winner and how many votes for that winner. Post back if you need more or if my assumptions are wrong. HTH Otto Sub GetBest() Dim rRngRow1 As Range, i As Range Dim HowMany As Long, What As Long HowMany = 0 Set rRngRow1 = Range("A1", Cells(1, Columns.Count).End(xlToLeft)) For Each i In rRngRow1 If Application.CountIf(rRngRow1, i.Value) HowMany Then HowMany = Application.CountIf(rRngRow1, i.Value) What = i.Value End If Next i MsgBox "Car number: " & What & Chr(13) & _ "How many: " & HowMany End Sub "Rich DeVito" wrote in message ... I am trying to solve a scoring problem for a car show. In a spread sheet we will have entry number of cars running across columns that represent individual 1st place choices. Without filtering and resorting the data (no time at the car show) I need to dynamically determine the top three entires and return the number of times they show up. I.E if these are the car entry numbers in their respective columns. 1 2 1 3 2 4 2 4 1 1 1 4 2 Should return 1 5 2 4 4 3 I don't even need the second number (number of votes, but we probably need it to verify the formula is working). Thanks!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the number of entries of specifc criteria is met | Excel Worksheet Functions | |||
Count, number of entries of a particular month. | Excel Worksheet Functions | |||
count the number of cell entries after filtering | Excel Discussion (Misc queries) | |||
How can I count the number of entries in a column? | New Users to Excel | |||
Count number of different entries? | Excel Worksheet Functions |