Attempting to return a unique value from a list based on multiple rankings.

I have a sheet of users who have made a 1st 2nd and 3rd choice for a number 1 to 60, each choice can only be assigned to one user. Each user is ranked in a separate table by 2 columns. In the first column the lowest number yields the highest rank, if there is a tie, then the second column must be taken into account and it is a percentage where higher is better.

I am trying to come up with a formula to take into account the rankings and then return the appropriate choice. So if a user picks 3 as their first choice and they are ranked the highest, then 3 is returned. Or if the user is not ranked the highest then their 2nd choice is calculated and if they are the highest ranking for it the 2nd choice is returned, failing over to the 3rd choice if need be.

Arrays are not my strong suit but I have tried a few different methods through various google adventures. I would imagine I need to get a array of matching 1st choice selections, then do the ranking calculation and return the value if highest ranked, or move to a new array for the second option.

I am currently just using a index function I found previously that lists the matches in separate cells, then vlookups the rankings and I visually assign the choice, but that is becoming quite tedious.

I am attaching a example spreadsheet, any help would be greatly appreciated.


example.xlsx

Hi check the attached sheet. It may help you. I solved it with supporting column only.

Originally Posted by Sindhus

Hi check the attached sheet. It may help you. I solved it with supporting column only.

This is perfect! I was going about it in all the wrong ways with my feeble attempts.

Thank you very much sir.

One more quick question.

It seems when I have 3 of the same choice selections (in the same order) the assigned func sets both the 2nd and 3rd ranked user to choice 2. Screenshot attached.

Example.JPG

Check this one.

Works perfectly as far as I can tell! Just wondering though, I paste the functions for all listed users (some of which dont have choices yet). The 2 highest ranking users without choices input yet will have numbers in their "calculation" fields. This shouldnt have any bearing on the accuracy of the output correct?

Once again thank you very much for your time.

Very sorry to bug you again, but found one more thing. It appears when a users assign_func is set to their second choice due to their lower rank, and then another users chooses the same number as their primary, both assign_func's end up with the same score. Image attached.

Example.JPG

I changed the Choice1 calc to the following and it appears to be factoring in the 2nd and 3rd choices. Feel free to let me know if this isn't the appropriate fix.

=IF((COUNTIF(Choices!$B$2:$B$6,Choices!B2)+COUNTIF(Choices!$C$2:$C$6,Choices!B2)+COUNTIF(Choices!$D$2:$D$6,Choices!B2))=1,1,IF(SUMPRODUCT(--(Choices!B2=Choices!$B$2:$B$6),--('User Ranking'!D2<'User Ranking'!$D$2:$D$6))=(COUNTIF(Choices!$B$2:$B$6,Choices!B2)+COUNTIF(Choices!$C$2:$C$6,Choices!B2)+COUNTIF(Choices!$D$2:$D$6,Choices!B2))-1,1,0))

Is your problem resolved with your fix?

I have actually run into another problem, probably due to my poor formula additions to the originals.

I was trying to make it where if a user is bumped down to their 3rd choice, but outranks someone who picks that same choice as their first, they still retain their choice and the person they outrank is bumped to their second choice.

The current sheet I have seems to skip a users first choice if anyone who out ranks them has it as their 2nd or 3rd choice, even when that 2nd or 3rd choice was not used.

I am probably doing a very poor job of explaining so I am attaching an example again.

example.xls

I have a doubt. In the attached sheet, user one, two and four are ranked higher than user 3. User 3 has got his first choice as 5. But 1,2 and 4 has got their second choice as 5. Should 5 go to one of 1,2 and 4 or, should it be given to user 3?

In your attached sheet, UserFour should have the 5 assigned to them since they have the higher ranking. The position of the choice (1, 2, or 3) shouldn't have any bearing on the output.

My desired output of the formula for "Assigned Func" for your attached example would be:

I2: 3
I3: 1
I4: 6
I5: 5
I6: 14

Do you want a formula solution? i got a vba code. Instead of complicating with formula, i thought this would help. Make sure in A column, after the user names, the cells are empty.

Please Login or Register  to view this content.

The only problem i found with the formula solution is the users are not in sorted order. As per your requirement, the users with higher rank should be given higher priority. So, i used a supporting column to sort the user rank and found their choices. Then used index-match combination to assign it to the users. Check the attached sheet.