Google-sheets – Duplicate values in dynamic sorting

duplicategoogle sheetsgoogle-sheets-arrays

I have an issue with duplicate sorting values for a formula in Google Sheets.
I'm using two tables to order 10 scores (see pic). The values in Column D are dragged from another sheet. The values in Column B have a simple RANK formula based on Column D:

=rank(D4,D$4:D$13)

The values in the second table are based on the first in order to dynamically order the second table.

Formula in Column G using an INDEX and MATCH:

=index($C$4:$C$13,match(H4,$D$4:$D$13,0),1)

Formula in Column H uses a simple LARGE formula:

=large($D$4:$D$13,1)

Which all works fine until the values in Column D are the same and the formula in Column G repeats the first value it comes across in Column C (see attached file).

Anyone have a solution for when there are duplicate values in dynamic sorting?

enter image description here

enter image description here

Best Answer

The problem to be resolved is that neither the RANK command, nor the formula in column G are designed to recognise and avoid a duplicate values.

Both ROC and Fod both scored 17, but there is nothing to distinguish between them.

Thankfully Prashanth at infospired.com recognised the underlying problem and solved it in "Flexible Array Formula to Rank Without Duplicates in Google Sheets".

Here's a link to my worked spreadsheet.

Here's a screenshot of Fergal's data versus my version (using the infospired solution). enter image description here

infospired created a new formula to create a "Rank without Duplicates". This is the formula in the screenshot:

=array_constrain(sort({row(D19:D28)-18,sort({row(D19:D28)-18,D19:D28},2,false)},2,true),9^9,1)

This is a complicated formula, but becomes easier to understand when it is broken into its component pieces. There are three steps:

  1. sort#1
  2. sort#2
  3. produce the ranking.

Sort#1 - sort({row(D19:D28)-18,D19:D28},2,false)
There are three steps
1 - Get the column of raw scores (as-is)
2 - Create a new column consisting of the numbers 1 to ten (inclusive), and assign one number to each score. Note this isn't ranking, it's simply assigning numbers to each score with no duplicates. The numbers are created using ROW with this code row(D19:D28)-18. "row(D19:D28)" would create the numbers 19 to 28 but there is a second element "minus 18". This has the effect of deducting 18 from each row number; so instead of creating number 19, it creates number one (nineteen minus eighteen); two instead of twenty; three instead of twenty one; four instead of twenty two and so on.
3 - User SORT to sort the columns by score descending.
enter image description here

Sort #2 - sort({row(D19:D28)-18,sort({row(D19:D28)-18,D19:D28},2,false)},2,true)
This also consists of three steps
1 - Take the columns from sort#1
2 - Create a new column consisting of the numbers 1 to ten (inclusive), and assign one number to row from Sort#1. The same method applies as in Sort#1. The ROW command generates row numbers, but "minus 18" reduces the actual sequence to numbers one to ten inclusive.
3 - Sort the output by the number column assigned in Sort#1-Step#2; and sort the column ascending.
enter image description here

Produce the ranking This step uses the ARRAY_CONSTRAIN command . The output is an array.

array_constrain(<<column output>>),9^9,1)

1 - The input is the results from Sort#2-Step3.
2 - Create an array up to 9^9 rows long. This is a LOT; scope for over 387 million rows.
3 - 1 column wide. This is the column assigned in Sort#2. This number is now a rank, but there are no duplicate values.

The final table can be created using VLOOKUP commands for both Name and Score. Base the lookup on the Rank, and use the Rank, Name and Score table as data to be interrogated. enter image description here

Related Topic