+ Reply to Thread
Results 1 to 3 of 3

Looking for a formula to rank data and output unique value

  1. #1
    Registered User
    Join Date
    08-26-2016
    Location
    Hawaii. USA
    MS-Off Ver
    Excel 2010, 2013, 2016
    Posts
    24

    Looking for a formula to rank data and output unique value

    Hi all,

    I'm hoping there's a simple solution for this one!


    I have a list of items with values. As an example, let's say the items are cars. Each car can have one driver.

    Problem: To pair the most valuable cars with the best suited driver available.



    Value Car Best Driver 2nd 3rd 4th Last
    62,000 Audi Jon Keith Tom Bill Paul
    48,000 BMW Bill Tom Rob Paul Greg
    23,000 Buick Greg Bill Paul Jordan Adam
    25,000 Ford Bill Jon Rob Tom Jordan
    32,000 GMC Jon Keith Adam Rob Tom
    42,000 Mustang Keith Rob Henry Jon Greg
    65,000 Tesla Bill Henry Keith Rob Jon
    28,000 Toyota Keith Adam Greg Henry Bill

    Selection Rank Formula Column (Best Avail Driver)
    BMW (3) Tom (Bill is taken)
    Mustang (4) Keith
    Tesla (1) Bill
    Audi (2) Jon
    Ford (5) Rob (Bill and Jon are taken)

    First, the formula needs to rank the cars, based on their value (I'm guessing the Rank or Countif formula)
    Then, find the preferred driver in the table array in the Best Driver column.
    If a higher ranked car has that driver, then move onto the next best driver (2nd). etc.

    Thanks in advance.

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    Re: Looking for a formula to rank data and output unique value

    Hi,

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This would help you to get the rank, but have a doubt how you identify the driver is available or taken??
    *If you wish you click on *,a way to say ThankYou

  3. #3
    Registered User
    Join Date
    12-26-2019
    Location
    seoul, south korea
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Looking for a formula to rank data and output unique value

    You need several steps to get the results you want.
    Please see below.

    a3
    =RANK(B3,$B$3:$B$10)

    i3
    =INDEX(D3:H3,MATCH(0,INDEX(COUNTIF(I$2:I2,D3:H3),),0))

    b14
    =INDEX($A$3:$A$10,MATCH($A14,$C$3:$C$10,0))

    c14
    =INDEX($I$3:$I$10,MATCH($A14,$C$3:$C$10,0))
    Attached Images Attached Images

+ 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. Replies: 12
    Last Post: 09-24-2018, 12:19 PM
  2. [SOLVED] How can I modify my array TEXTJOIN formula to output only unique values
    By Victorjo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-06-2018, 01:50 AM
  3. [SOLVED] Rank text alphabetically with unique rank from 1, 2, 3, 4
    By Xenthys in forum Excel General
    Replies: 12
    Last Post: 10-01-2017, 12:03 PM
  4. Rank data and output data based on rank
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-08-2017, 12:28 PM
  5. Rank data in excel 2007 in descending order with unique values
    By Malinda in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-02-2017, 04:27 AM
  6. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  7. RANK - Non unique values, contiguous rank required
    By PaulBo in forum Excel General
    Replies: 9
    Last Post: 02-20-2014, 10:34 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