#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    5
    Rep Power
    0

    Microsoft Excel - Return the Next of a Duplicate


    Anyone ever written a formula that gave you the next available answer for a duplicate value?

    I have two columns of data:

    TEAM (unique value)
    STAT (non-unique value)

    I created a ranking list that ranks the STAT column in order from Best to Worst. I used the LARGE formula in order to do this.

    Then next to the Ranked Data, I created a new formula that would look for the Ranked Value in the STAT column and respond back with the corresponding TEAM. In this instance I used the INDEX/MATCH formula.

    HOWEVER, I have duplicate values and because of that, my INDEX/MATCH formula returns the same value.

    An example of this would be:

    68.5 Panthers
    67.4 Cowboys
    65.1 Colts
    63.7 Redskins
    63.7 Redskins

    In this case, instead of returning the Steelers as the next result because the Redskins and Steelers both contained the same value, it returned Redskins because each instance of the formula stops on the first available match. I am trying to get it to return the next TEAM that has the same value.

    I will send my spreadsheet to anyone that asks. Mess with it how you want. I've actually been trying to find a formula that does this for the longest time now.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2007
    Posts
    1
    Rep Power
    0
    you'll have to rank your non-unique numbers first so that they each get a unique rank.

    in a new column enter:

    =SUM(1*(a2<$a$2:$a$20))+1+IF(ROW(a2)-ROW($a$2)=0,0,SUM(1*(a2=OFFSET($a$2,0,0,INDEX(ROW(a2)-ROW($a$2)+1,1)-1,1))))

    enter using Ctrl+Shift+Enter
    then copy down the column

    then you can use your rank column to index instead.

    hope this helps.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    duplicate values


    I used DuplicateFilesDeleter so don't have idea about that.

IMN logo majestic logo threadwatch logo seochat tools logo