count array for top three entries and return their number

[Rich DeVito]

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!!

 

[Otto Moehrbach]

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

 

[Otto Moehrbach]

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

 

[T. Valko]

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)

 

[Rich DeVito]

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!