# Formulas to distribute ranked items

1. ## Formulas to distribute ranked items

This is a simplification of what I really need. But say I have 5 people, and I have 5 gifts (A-E) Each person is in order by who gets first choice, second, etc.
And each person has indicated from 1 - 5 which gift they like best.
1=most favorite 5=least favorite

I'm trying to create formulas to show who gets what. My actual spreadsheet has more people/items but is virtually the same thing.

In the attached example,the first person, Bill, gets first choice, then Mary, then Karl etc.

The spreadsheet itself could be done differently if there's a easier/better way to get the data.

And, I am not very familiar with VBA, so hopefully there are regular formulas that will do it. Thanks!

2. ## Re: Formulas to distribute ranked items

See attached file (I used 5 as most favourite and thus looking for max od sumproduct).
Best Regards,

PS. Check if boundary condition for changed values is binary - I have non-english version of Excel.

3. ## Re: Formulas to distribute ranked items

Thanks Kaper. Unless I've misunderstood your reply, though, I don't think that is it.
Pardon my difficulty in describing the issue.

I'm looking for formulas to calculate which person will get which gift.
The people are listed in the order of choosing, and below each person is how they ranked the gift choices.

Bill is first so he gets his first choice.
His number 1 choice was Gift C, so that's what he gets.

Mary gets to choose second.
Her #1 choice is gift A, and since it isn't Gift C which has already been taken, that's what she gets.

Karl gets to choose third.
His first two choices have already been taken by Bill and Mary
So Karl gets his #3 choice, Gift D

4. ## Re: Formulas to distribute ranked items

OK, this is rather my misunderstanding. I thought about giving to everybody as good gift as possible).
And as you describe it is indeed different situation.
I could imagine solution with formulas, but using the second table, same size as the main one.
May be let's try macro then:

``Please Login or Register  to view this content.``
if there is more (or less) than 5 persons (and gifts) just change to_select constans

5. ## Re: Formulas to distribute ranked items

Another way:

 A B C D E F G H I J K L 1 Gift Bill Mary Karl Jan Peter Bill Mary Karl Jan Peter 2 A 5 1 1 5 2 A A 3 B 4 2 4 4 1 B B B B B 4 C 1 3 2 3 4 C 5 D 3 5 3 2 3 D D D 6 E 2 4 5 1 5 E E E E 7 8 Choice C A D E B

In H2 and copy down and across:

=IF(COUNTIF(\$G\$8:G\$8, \$A2)=0, \$A2, "")

In H8 and copy across:

=INDEX(H2:H6, MATCH(SMALL(IF(H2:H6 <> "", B2:B6), {1,2,3,4,5}), B2:B6, 0))

6. ## Re: Formulas to distribute ranked items

Hi shg,
This is exactly what I described as:
Originally Posted by Kaper
using the second table, same size as the main one.
I tried to wrap it into one array formula, (not using H2:J6 at all), but was not successfull.

7. ## Re: Formulas to distribute ranked items

Or, without the helper table,

 A B C D E F 1 C A D E B 2 Gift Bill Mary Karl Jan Peter 3 A 5 1 1 5 2 4 B 4 2 4 4 1 5 C 1 3 2 3 4 6 D 3 5 3 2 3 7 E 2 4 5 1 5

In B1, confirm with Ctrl+Shift+Enter and copy right:

=INDEX(\$A\$3:\$A\$7, MATCH(SMALL(IF(TRANSPOSE(COUNTIF(\$A\$1:A1, TRANSPOSE(\$A\$3:\$A\$7)))=0, B3:B7), {1,2,3,4,5}), B3:B7, 0))

8. ## Re: Formulas to distribute ranked items

yeah, I was pretty close with my arrayformula construction ...

9. ## Re: Formulas to distribute ranked items

Thanks Everyone - these are great! Using INDEX is new to me so something new to research and play around with, and I plan to try the macro solution too. (I love watching macros do their thing.) This is going to do the job quite nicely!

10. ## Re: Formulas to distribute ranked items

Just loaded the macro and ran it - worked like magic!

11. ## Re: Formulas to distribute ranked items

You're welcome.