November 18th, 2006, 10:11 PM
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:
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.
September 7th, 2007, 04:18 PM
you'll have to rank your non-unique numbers first so that they each get a unique rank.
in a new column enter:
enter using Ctrl+Shift+Enter
then copy down the column
then you can use your rank column to index instead.
hope this helps.
February 13th, 2014, 01:18 AM
I used DuplicateFilesDeleter so don't have idea about that.