CODEWITHSUNDEEP
google-sheetsduplicatesdense-rank
Akirah A.
Akirah A.

Reputation: 25

How to get the dense rank from the range that the conditions are matched without skipping if the range contains duplicates in Google Sheets

I'm now working on Google Sheet and I can't figure this problem out myself. I've tried many functions like IF(COUNTIF(....)) to find the unique values and SUMPRODUCT() to do the dense rank but all of this can't give me what I really want. I hope that you guys could help me fix this.

I have the sheet that contains the values with some duplicates like this :

enter image description here

I want to do the dense rank like this :

enter image description here

How can I do that in the Google Sheet?

Here is the link to the Google Sheet : Link

Please help me figure this out. Thank you in advance.

Upvotes: 0

Views: 238

Answers (1)

z..
z..

Reputation: 13013

Here's a possible solution:

=ARRAYFORMULA( 
   LET(class,A2:A,
       round,C2:C,
       score,D2:D,
       ranks,IFNA(
              BYCOL(
                {".",REGEXREPLACE(F1:I1,"(?s)Class (\w+)|(\w+) Round|.","$1$2")},
                LAMBDA(header,
                  LET(score_,IF(REGEXMATCH(class&round,header),score,),
                      XMATCH(score_,SORT(UNIQUE(score_),1,FALSE)))))),
       FILTER(ranks,class<>"")))

This formula is entered in cell E2 and it produces the results for the whole table using the header F1:I1 as reference for the classes/rounds to consider.

For each column, it's essentially sorting the unique scores and using XMATCH to return the rank. The order of the rank can be easily inverted by changing FALSE (inside the SORT function) to TRUE.

Upvotes: 5

Related Questions