+ Reply to Thread
Results 1 to 11 of 11

Formulas to distribute ranked items

  1. #1
    Registered User
    Join Date
    08-01-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Formulas to distribute ranked items

    Our Favorite Gifts.xlsx

    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.
    Bill would get Gift C.
    Mary would get gift A
    Karl would get Gift D because gift C and A are already taken.

    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!
    Last edited by lzuke; 01-07-2014 at 03:55 PM.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Formulas to distribute ranked items

    How about using Solver?
    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.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-01-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    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
    Last edited by Kaper; 01-08-2014 at 12:11 PM.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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))
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Formulas to distribute ranked items

    Hi shg,
    This is exactly what I described as:
    Quote Originally Posted by Kaper View Post
    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. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Formulas to distribute ranked items

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

  9. #9
    Registered User
    Join Date
    08-01-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    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. #10
    Registered User
    Join Date
    08-01-2012
    Location
    Maryland, USA
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formulas to distribute ranked items

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

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formulas to distribute ranked items

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find unique entries and create ranked list with formulas only
    By opheim in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-08-2013, 04:09 AM
  2. Extract a sorted and ranked unique list of items
    By bruno_ in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-11-2013, 09:52 PM
  3. Listing Ranked items, with multiples of the same rank
    By Befuddled in forum Excel General
    Replies: 2
    Last Post: 08-14-2011, 02:23 AM
  4. Replies: 1
    Last Post: 11-29-2010, 09:09 PM
  5. Match items in two ranked lists
    By jim e. in forum Excel General
    Replies: 5
    Last Post: 10-01-2009, 09:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1