0

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")
0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.