+ Reply to Thread
Results 1 to 5 of 5

Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

  1. #1
    Registered User
    Join Date
    12-07-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

    Hello Excel world,

    Thanks in advance for the help. I have attached an Excel file ** Duplicates Problem Example 1.xlsx ** to better explain my problem. I am currently tracking the amount of work completed by different employees in week-by-week increments. I then have tables set up to have Excel calculate and find who some of the "Top Performers" are and list, by ranking, both the amount of work they completed and their name.
    I am using a "LARGE(" function to find the amount of work completed from the list and an "INDEX(MATCH(" function to match the employee's name to the amount of work they completed.
    The issue is when there is more than one person who completed the same amount of work. The "INDEX(MATCH(" function simply finds the first name that matches that amount of work completed and will show his/her name multiple times instead of finding the next employee in the list who completed that amount of work (Such as Employee 15 showing up twice in the "Past 5 Days Top Performer Table").
    I would like to use a different function or add something on to this one so that it recognizes when the names are duplicated and Excel will provide the next name in the list that completed that amount of work.
    I messed around with another table and some IF( functions but I'm hopeful someone more knowledgeable than me will be able to help.

    Thank you!

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

    How do you want the tied ranking's differentiated ? by alphabetical name,by first in list...etc..) in other words...what breaks a tie ?
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

    Hi and welcome to the forum

    This might be an approach to try. I only did this for column E and for the 2nd table, but you can apply the same principle to the other columns/tables. What I did was to test for duplicates, and if found, to increase their score by 0.1 (you could use 0.01 if there will be lots). If there is no duplicate, it leaves the score as is. I used this in helper column F, copied down...
    =IF(E6=0,0,IF(COUNTIF($E$6:E6,E6)>1,E6+COUNTIF($E$6:E6,E6)*0.1,E6))

    Then I used this in the Employee column in the 2nd table, copied down...
    =INDEX(A$6:A$29, MATCH(LARGE(F$6:F$29,ROW(A1)),F$6:F$29,0))

    You can hide the helper columns so that they dont clutter up things

    Let me know if this is something you can work with?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    12-07-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

    Those formulas work great! The only change I made is I removed the first $ from the first formula when it uses "COUNTIF($E". Because I wanted to make other helper columns for the other three weeks this let me copy and paste this formula to the other columns as well. This works better than I expected, thanks!

  5. #5
    Registered User
    Join Date
    12-07-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Alternatives to "INDEX(MATCH(" Function when Duplicates Exist

    And because the employees will be listed in alphabetical order these formulas will allow ties to be broken my the alphabetical order of their names, which is a good point dredwolf. Thanks again, SOLVED!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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