Excel / VBA formula for most voted decision

spinar

New Member
Joined
Sep 13, 2017
Messages
6
Dear All,

I would like to request help for excel ro VBA formula that will count for votes given to employees and showing the most voted decision. Such as

Employee Supervisor 1 Supervisor2 Supervisor3 Supervisor4
Harry Promote Promote Stay same Demote


In this case, formula should show "Promote" because majority voted for Promotion. If there is a split (like 2 promote and 2 demote) then formula should show split.

Is this possible??
Thank you very much in advance..

spinar
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Try this

Code:
Function Votes(r As Range) As String
Dim cel As Range
Dim pCount As Integer
Dim sCount As Integer
Dim dCount As Integer
Dim Arr(1 To 3, 1 To 2)
Dim ts As String
Dim tn As Integer
Dim High As Integer
Dim Res As String
For Each cel In r
    Select Case cel.Value
        Case "Promote"
            pCount = pCount + 1
        Case "Demote"
            dCount = dCount + 1
        Case "Stay Same"
            sCount = sCount + 1
    End Select
Next cel
Arr(1, 1) = pCount
Arr(1, 2) = "Promote"
Arr(2, 1) = dCount
Arr(2, 2) = "Demote"
Arr(3, 1) = sCount
Arr(3, 2) = "Stay Same"

For i = 1 To UBound(Arr)
    For j = i To UBound(Arr)
        If Arr(i, 1) < Arr(j, 1) Then
            tn = Arr(i, 1)
            ts = Arr(i, 2)
            Arr(i, 1) = Arr(j, 1)
            Arr(i, 2) = Arr(j, 2)
            Arr(j, 1) = tn
            Arr(j, 2) = ts
        End If
    Next j
Next i
High = Arr(1, 1)
For k = 1 To UBound(Arr)
    If Arr(k, 1) = High Then
        Res = Res & Arr(k, 1) & " " & Arr(k, 2) & ", "
    Else
        Exit For
    End If
Next k
Res = Left(Res, Len(Res) - 2)
Votes = Res
End Function

And assuming your data is in cells A1:F2, the formula would look like this. =Votes(B2:E2)
 
Upvote 0
Hi again All,

I just wanted to show in excel format..

EmployeeEmployeeEmployee
HarryHarryHarry
VotesORVotesORVotes
Supv1PromoteSupv1DemoteSupv1Demote
Supv2PromoteSupv2DemoteSupv2Demote
Supv3DemoteSupv3PromoteSupv3Promote
Supv4StaySupv4PromoteSupv4Promote
Supv5PromoteSupv5StaySupv5Stay
Supv6DemoteSupv6StaySupv6Stay
Supv7PromoteSupv7DemoteSupv7Demote
Supv8StaySupv8DemoteSupv8Demote
Supv9StaySupv9DemoteSupv9Promote
Supv10PromoteSupv10PromoteSupv10Promote
ResultPromoteResultDemoteResultSplit

<tbody>
</tbody>
 
Upvote 0
Resolving the ties is the tricky bit. If you're prepared to use a couple of helper columns then you can do it with formulas:


Book1
ABCDEFGH
1EmployeeSupervisor 1Supervisor 2Supervisor 3Supervisor 4ResultHide meHide me
2HarryPromotePromoteStay sameDemotePromotePromotePromote
3HarrietDemoteDemotePromotePromoteSplitDemotePromote
Sheet1
Cell Formulas
RangeFormula
F2=IF($G2=$H2,$G2,"Split")
G2{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:G2))),"")}
H2{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:H2))),$G2)}
Press CTRL+SHIFT+ENTER to enter array formulas.


WBD
 
Last edited:
Upvote 0
Resolving the ties is the tricky bit. If you're prepared to use a couple of helper columns then you can do it with formulas:

ABCDEFGH
1EmployeeSupervisor 1Supervisor 2Supervisor 3Supervisor 4ResultHide meHide me
2HarryPromotePromoteStay sameDemotePromotePromotePromote
3HarrietDemoteDemotePromotePromoteSplitDemotePromote

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
F2=IF($G2=$H2,$G2,"Split")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G2{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:G2))),"")}
H2{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:H2))),$G2)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



WBD


Hello WDB,

Thank you very much for this.. It works like a charm.. If I have 6 supervisors, how shall i change the formula??

spinar
 
Upvote 0
Just extend the formula to the other columns like this:


Book1
ABCDEFGHIJ
1EmployeeSupervisor 1Supervisor 2Supervisor 3Supervisor 4Supervisor 5Supervisor 6ResultHide meHide me
2HarryPromotePromoteStay sameDemotePromoteStay samePromotePromotePromote
3HarrietDemoteDemotePromotePromoteStay sameStay sameSplitDemotePromote
Sheet1
Cell Formulas
RangeFormula
H2=IF($I2=$J2,$I2,"Split")
I2{=IFERROR(INDEX($B2:$G2,INDEX(MODE.MULT(MATCH($B2:$G2,$B2:$G2,0)),1)),"")}
J2{=IFERROR(INDEX($B2:$G2,INDEX(MODE.MULT(MATCH($B2:$G2,$B2:$G2,0)),2)),$I2)}
Press CTRL+SHIFT+ENTER to enter array formulas.


WBD
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top