Google Spreadsheets-Autosort but with a second argument

Question

This is the link to a Google Spreadsheet for use in a fishing club.

https://docs.google.com/spreadsheets/d/17xFDHoF_-3Kg7jVrxiIRjBJy-DD70RFg6wPf19fQC50/edit#gid=943629989

When the fishing tournament starts, all the names of the competetors (Leden) en their seat (zitplaatsen) can be selected from a list and are being automatically filled in in the corresponding cells. After the tournament is finished the weight of the fish has to be filled in in 2 columns. One column for the kgs and one row for the grs. With the SORT formula the data are being sorted from high to low. The one who cathed the most fish is obviousely the winner of the tournament. So far, no problem. The problem starts here:when 2 or more competetors catch the same amount (weight) of fish, the data has to be re-evaluated; the one in seat 2, for instance, wins from the one in seat 3 (so the higher the number of the seat, the lower you get ranked). This is also for instance for seats 14,17 and 18; if there weight is the same, number 14 is higher ranked then 17 and 18. Seat 18 is then lower ranked than 17. The reason herefor is that when they start to weigh the fish, they start at seat 1. The net is clean then. After 1 they weigh 2, then 3 and so on...The further they go, the more algue they collect in the net, that is being weighted also, but is no fish. So the last seat has a lot of weight that includes fish and much algue.

Please, can someone help me integrate the correction in the spreadsheet ?

Thanx in advance !

Answer 1

Create the sorted results using a query. This query is located in the same sheet as the raw data, but the query can be created on any sheets (such as the Results Sheet).

Locations using decimal points

={query({"Result";A10:A27}),query(B9:F27,"select * order by C desc, D desc, E asc")}

Locations using decimal commas

={query({"Result";A10:A27})\query(B9:F27;"select * order by C desc, D desc, E asc")}

NOTE: Avoid merging rows and columns in the sheet containing the raw results. This can cause unexpected results.

---

Sample

Answer 2

is this what the formula you're trying to use:

=sort(A3:C8,2,false,3,false,1,true)

BEFORE/AFTER

---

SORT Documentation