Logic to find winner of an award

[ncbf87]

Hi all,

I have a monthly set of data = nominations from anyone in my company, on another employee that has demonstrated certain company values. This set of nominations will then go to a committee for selection of a monthly employee award.

I am tasked to help reduce down the size of the monthly nominations (N>300) so that the committee has an easier time to finalise the 20 winners, instead of going through all lines. Ideally, best if I can help sort out the 20 winners and them just approving it.

Criteria for selection:

Number of nominations for the month (highest has more chance to win) - done this

Exclude any nominations that had won an award in the last 6 months - done this

Apply a check/logic to ensure no one business unit dominates the list (winners need to be spread across). Maybe a reference table that can be manipulated (modelling) - trouble with this

Apply a check/logic to ensure all 6 company values (highlighted in yellow) are include represented in the 20 winners - trouble with this

Can I get your kind help to give me an idea on how I can complete criteria 3 and 4 above? I've seen a macro used before with the usage of "log" to achieve something similar, but I do not understand "log" enough of it to re-create the macro**.**

These are the raw data headers

Short comment = values (standard drop down)

Long comments = elaborate/remarks of why this employee deserve an award.

npnkrl2i5xk31.png
I've worked out a table as following attached



Thanks!