Ranking and Sorting data without the RANK and SORT functions

From the makers of getting UNIQUE values from a range, here’s another brain-melting approach on being able to rank and sort data on a spreadsheet — enjoy :smiling_imp:

Context

There’s a good chance you may only be looking for a way to sort data (programatically) using Rows and might’ve simply wanted to make use of the obvious SORT function.
Something like this -

Problem

While we’re working on building our own SORT function :crossed_fingers:t4:, it has been virtually impossible to wrangle the data in this format — until now :fire:

:warning: Given the approach, it is improbable to have sorted a range of data, unless it has been ranked and so, we’ll first be looking at how to accomplish that, which you’d typically expect to having done using the RANK function in Google Sheets.
Something like this -

You guessed it though, we don’t have a RANK function at the moment either :sweat_smile:

Solution

Overview

  1. Ranking the data
  2. Creating a helper column that lets us know how many times a rank is being repeated
  3. Setting up a sorted column of the rank
    • this step is at the core of the solution where we’ll first be “sorting” the rank from top to bottom and then align the right data points against each of the “ranked” row
  4. Finally, fetching the column data against each of the sorted (by rank) row :muscle:t4:

1. Ranking the data

Thanks to this solution on Stack Overflow, I was able to adapt the formula to how I wanted it to work.

Solution: =COUNTIF(B:B,">"&B2)+1

2. RankCount helper column

Like most workarounds, this solution too requires some additional metadata and here, we’ll simply be assessing the number of times a rank has been repeated using the COUNTIF function -

3. Building the Rank’d column

This step could seem disorienting and so here’s a preview of what we’re trying to achieve -

As you can tell, Column A from the above screenshot (Sorting table) has the same set of data as Column C from the snapshot above this one (Ranking table) but with a difference that it has been aligned from top rank to the bottom one and the formula that sits inside cell A1 is -

=IF(COUNTA($A$1:A1)=COUNTA('Rank'!C:C),"",IFERROR(IF(PARSE(RANGE2JSON('Rank'!$C$2:$C,'Rank'!$D$2:$D),"[0].['"&ROW(A1)&"']")=1,ROW(A1),A1+COUNTIF($A$1:A1,A1)),IF(ROW(A1)=1,1,A1)))

Let’s break this down:

  • the first part [=IF(COUNTA($A$1:A1)=COUNTA('Rank'!C:C),"",] simply tells the cell to return blank if it has been dragged down beyond the number of rows from the actual dataset
  • the IFERROR function after that is being used to handle 2 things:
    • first is if there are more than one instance of rows with rank 1
    • second is when there are more than one instance of rows for all other ranks
  • :woman_mage:t4: the real magic is being done by the RANGE2JSON function because when we allow numbers to be the “headers”, it automatically deduplicates and also sorts itself from low to high
    • the count from helper function (which serves as the values for RANGE2JSON) accordingly lets us know how many times the header value (i.e. the rank) is being repeated
  • With the PARSE function, the formula is trying to retrieve the object number based on the ROW number of the cell above it as the valid values would be part of the RANGE2JSON data
  • the IF function ultimately controls which logic to be applied based on the single occurrence of a specified rank or multiple ones

4. Retrieving data against respective ranked & sorted rows

In this step, we’ll be running RANGE2JSON along with the PARSE function to lookup and filter data using multiple criteria :innocent:

Formula in cell B2 from the screenshot above -
=IF(COUNTIF($A$2:$A,$A2)=1,PARSE(PARSE(RANGE2JSON('Rank'!$A$1:$C$1,'Rank'!$A$2:$C),"$.[?(@.Rank == "&$A2&")]"),"[0].['"&B$1&"']"),PARSE(PARSE(PARSE(RANGE2JSON('Rank'!$A$1:$C$1,'Rank'!$A$2:$C),"$.[?(@.Rank == "&$A2&")]"),"["&COUNTIF($A$1:$A2,$A2)-1&"]"),"['"&B$1&"']"))

Here’s the breakdown of the formula:

  • the IF function controls whether there’s either a single instance of data for a specified rank or more
  • depending on that, the PARSE operation is slightly modified to accommodate fetching data from a single object or multiple ones
  • in cases where there could be multiple rows against the same rank, a COUNTIF determines which object ought to be placed against which row and is in the same order as the original data

Demo

If you’ve reached so far down this post, you deserve to experience this set up first hand :smile: so here you go -
https://rows.com/Sourabh-Choraria/editor/a67f4f20-8593-11eb-b0cc-cb28d6f13327/f6cc00e0-8598-11eb-b1eb-03d8280eb11e/live

Feel free to DM or comment in case you need any assist with having this implemented in your spreadsheet.

In case you just landed on this article and don’t yet have an account with us, do sign up here to get started!

Cheers :clinking_glasses:
Sourabh