Google-sheets – Advanced sorting Google Sheets

google sheets

I am dealing with a sorting problem that I have. Now I do it manually but I would like to do it automatic.

So I have 7 columns and loads of rows.

First I sort all the rows so the biggest value in column A is on top-
Then in column B I give the top row a 1, second row a 2 and so on.

Then I sort the all the rows so that the biggest value of column C are in the top.
After that I do the same, in column D and row 1 I give that one value 1, next row value 2 and so on.

Then in Column E I add the numbers from column B and D together and sort Column E from so the lowest value are in the top.

After that I take the top 20 Rows and sort once more with column F and the biggest value in the top.

It's a wee bit complicated sorting system and I do it on a regular base.

Dose anybody have a good idea how to do this a bit more simplistic?

EDIT:

So I have now down 5 different sheets, not the prettiest solution.

On the first I have

QUERY('data_2020-06-07'!A1:I998;"select A, B, C, D, E";1)

Second

QUERY('Sort 1'!A1:F249;"select A, B, C, D, E, F order by F desc";1)

Third

QUERY('Sort 2'!A1:G308;"select A, B, C, D, E, F, G order by D desc";1)

Forth

QUERY('Sort 3'!A1:I1000;"select * where I <> '' or I is not null order by I Limit 20")

Fifth

QUERY('Sort 4'!A1:I1000;"select * where E <> '' or E is not null order by E DESC Limit 20")

Best Answer

You are sorting various fields, manually ranking results and combining rankings to yield an outcome.

The following solution is not perfect (IMHO a perfect solution would be a single, fully-integrated formula), but it is good - it does remove all the manual processing and can be setup to operate repetitively. Possibly (probably!) others might be capable of integrating this into a single formula, but perhaps this will meet your needs until then.

Ranking

The key to the solution is the ranking element. You describe two instances of this, and a third variation:

  • "in column B I give the top row a 1, second row a 2 and so on.",
  • "I do the same, in column D and row 1 I give that one value 1, next row value 2 and so on."
  • "in Column E I add the numbers from column B and D together"

I have used the ranking method described in Using Google sheets Query how to Rank along with Partition and Order by Data on Google Docs Help. I hesitate to attempt a description of how this works - even the author of the original post remarked "[I] am not going to explain [this] in detail as [I] will end up needing a whisky bottle". Suffice to say - it works.

You used Columns B, D & E because you manually inserted ranks in those cells. In this solution, I have retained those columns (for the sake of comparability), but I do not use them. Each of the ranking columns is appended to the right-hand end of the query. This should enable you to delete columns B, D & E if you wish.

Output: for the sake of simplicity, insert a new sheet in the same spreadsheet as "data_2020-06-07". We'll call this new sheet "Sort".

Query#1: sorted on Column A (descending), ranking on Column A

  • In sheet="Sort", cell A1, insert this formula : =ArrayFormula({query('data_2020-06-07'!A1:I1001,"select * order by A desc"),QUERY({"ColA Rank";If('data_2020-06-07'!A2:A1001="",,row('data_2020-06-07'!A2:A1001)-row('data_2020-06-07'!A2)+1)})})

Query#2: sorted on Column C (descending), ranking on Column C

  • In sheet="Sort", cell K1, insert "'query#2=>"
  • In sheet="Sort", cell L1, insert this formula : =ArrayFormula({query(A1:J1001,"select * order by C desc"),QUERY({"ColC Rank";If(C2:C1001="",,row(C2:C1001)-row(C2)+1)})})

Create Column E: Add ranking Column A and Column C (refers to column E)

  • In sheet="Sort", cell W1, insert this formula : ={"ColA+C Rank";arrayformula(If(U2:U="",,U2:U+V2:V))}

Query#3: sorted on Column W (sum of Column A and Column C ranking) ascending

  • In sheet="Sort", cell X1, insert "'query#3=>"
  • In sheet="Sort", cell Y1, insert this formula : =query(L1:W1001,"select * order by W asc")

Query#4: sorted on Column AD (refers to Column F)

  • In sheet="Sort", cell AK1, insert "'query#4=>"
  • In sheet="Sort", cell AL1, insert this formula : =query(Y1:AJ1001,"select * order by AD desc limit 20")

Of course, you could create a new sheet for Query#4 so that the column headers align with with the Columns.

Related Topic