Excel / VBA formula for most voted decision

S

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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..

[TABLE="width: 779"]
<tbody>[TR]
[TD]Employee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Employee[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Harry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Votes[/TD]
[TD][/TD]
[TD]OR[/TD]
[TD][/TD]
[TD="colspan: 2"]Votes[/TD]
[TD]OR[/TD]
[TD][/TD]
[TD="colspan: 2"]Votes[/TD]
[/TR]
[TR]
[TD]Supv1[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv1[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv1[/TD]
[TD]Demote[/TD]
[/TR]
[TR]
[TD]Supv2[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv2[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv2[/TD]
[TD]Demote[/TD]
[/TR]
[TR]
[TD]Supv3[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv3[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv3[/TD]
[TD]Promote[/TD]
[/TR]
[TR]
[TD]Supv4[/TD]
[TD]Stay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv4[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv4[/TD]
[TD]Promote[/TD]
[/TR]
[TR]
[TD]Supv5[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv5[/TD]
[TD]Stay[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv5[/TD]
[TD]Stay[/TD]
[/TR]
[TR]
[TD]Supv6[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv6[/TD]
[TD]Stay[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv6[/TD]
[TD]Stay[/TD]
[/TR]
[TR]
[TD]Supv7[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv7[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv7[/TD]
[TD]Demote[/TD]
[/TR]
[TR]
[TD]Supv8[/TD]
[TD]Stay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv8[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv8[/TD]
[TD]Demote[/TD]
[/TR]
[TR]
[TD]Supv9[/TD]
[TD]Stay[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv9[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv9[/TD]
[TD]Promote[/TD]
[/TR]
[TR]
[TD]Supv10[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv10[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Supv10[/TD]
[TD]Promote[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD]Promote[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD]Demote[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result[/TD]
[TD]Split[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
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
wideboydixon said:
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
EmployeeSupervisor 1Supervisor 2Supervisor 3Supervisor 4ResultHide meHide me
HarryPromotePromoteStay sameDemotePromotePromotePromote
HarrietDemoteDemotePromotePromoteSplitDemotePromote

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF($G2=$H2,$G2,"Split")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:G2))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B2:$E2,INDEX(MODE.MULT(MATCH($B2:$E2,$B2:$E2,0)),COLUMNS($G2:H2))),$G2)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



WBD
Click to expand...


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
You must log in or register to reply here.

Similar threads

C
  • Solved
FILTER function to return ONLY records with most recent date
Replies
6
Views
4K
Fluff
Fluff
Oakie_Uffda
  • Question Question
Split Cell into multiple without splitting words and print decent
Replies
1
Views
1K
Oakie_Uffda
Oakie_Uffda
D
  • Solved
Excel VBA to Split Period of Months into Individual Month with extra criteria
2
Replies
19
Views
3K
Peter_SSs
Peter_SSs
F
  • Locked
  • Question Question
Calculate dates based on complex conditions and produce an outcome- [Excel 2013, VBA/Formula]
Replies
1
Views
538
Fluff
Fluff
F
  • Question Question
Calculate the difference between multiple dates based on certain conditions [Excel 2013 Formula/VBA]
Replies
1
Views
540
floydschoice
F

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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