I have the following columns in a Google Sheets Table. In column A, I have the rank column.
I would like to have the rank value be based on whatever column is currently being sorted by. In this case, I'm sorting by Winrate.
Is this possible? I know this can be done with a specific column out of the whole sheet; I'm wondering specifically about if this can be done with a Table, which seems to have some unique properties aside from the standard row, columns. Notice I'm sorting by the Table column and not the sheet column.
There is no turnkey way for a formula to discover which column a table was most recently sorted by. It could be done by iterating all columns and finding which ones depict a monotonically increasing or decreasing sequence, but that would be a bit complex.
It would be much simpler to Insert > Sheet, add a dropdown to choose the column to sort by, and use a sort()
formula to copy the data table sorted by the column. Use match()
to find the column whose label matches the value in the dropdown. You can use that column number in the rank()
formula as well.
Another approach is to use simple ordinals instead of ranks. Since sorting brings the table in the order where the now current leader is at the top, and the rest follow, the ordinal in the first row would always be 1
, the next one 2
and so on. To add such sequence of ordinals, put this formula in cell A4
:
=row() - row(A$3)
...and copy the formula cell down the column. Note that the formula will not observe ties but will always show a simple strictly increasing sequence of ordinals.