Add Points and sort a column according to the ranking-CodePudding

I currently am working on a sheet where I have multiple categories, where each name can have different scores based on the category. Then points are given out based on their ranking and the value they got compared to the others.

In the end, I want to sum up all points for each name with ranking and value points separate and then sort the name-sum pairs based on each kind of sum total.

Here is a Link on a small version that I made based on what I am currently at, in my actual sheet:

If you had many more than three categories, this wouldn't be practical so you would need an alternative approach - maybe flattening the data to four columns and using a query to calculate the sums and sort them. Here is a sample formula (hard-coded for the case of 5 rows of data starting in row 3, 3 sets of 4 columns with an offset of 5 columns between them)

=ArrayFormula(query(vlookup(mod(quotient(sequence(15,4,0),4),5) 3,{row(A3:A7),A3:N7},mod(sequence(15,4,0),4) 2 quotient(sequence(15,4,0),20)*5,false),"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc label sum(Col3) ''"))

Note

If you use semicolons instead of commas, the formulas would be

=sort({A3:A7\sumif(A3:A7;A3:A7;C3:C7) sumif(F3:F7;A3:A7;H3:H7) sumif(K3:K7;A3:A7;M3:M7)};
2;0)

=sort({A3:A7\sumif(A3:A7;A3:A7;D3:D7) sumif(F3:F7;A3:A7;I3:I7) sumif(K3:K7;A3:A7;N3:N7)};
2;0)

and

=ArrayFormula(query(vlookup(mod(quotient(sequence(15;4;0);4);5) 3;{row(A3:A7)\A3:N7};mod(sequence(15;4;0);4) 2 quotient(sequence(15;4;0);20)*5);"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc label sum(Col3) ''"))

CodePudding user response：

For column 3 in your blocks, ie. ranking points,

=sort(transpose(query({A3:D7;F3:I7;K3:N7},"select sum(Col3) pivot Col1",1)),2,false)

For column 4 in your blocks, ie. value points,

=sort(transpose(query({A3:D7;F3:I7;K3:N7},"select sum(Col4) pivot Col1",1)),2,false)

In your locale, function inputs are separated by ; instead of ,. So replace the separators accordingly if Google Sheet doesn't do it automatically.

It appears that your column 4s have custom formatting. You can also apply formatting at the end but you may end up having rounding errors propagated. If you intend for the values in column 4 to be rounded, it is best to round them in the original blocks with round() or roundup().

Aside, unfortunately, query() does not inherit formatting the same way other native functions do.

Let's talk about why they work and why we need to use query().

For function definitions, consult official documentation and type the function name in the search bar. I'll go over their usage as applied to your example.

The sorting itself is straight forward. You use sort(). Suppose you have helper columns as follows:

in P3, put either =arrayformula(A3:A7) or unique({$A3:$A;$F3:$F;$K3:$K});
in Q3, put =sum(filter({C:C;H:H;M:M},{$A:$A;$F:$F;$K:$K}=$P10))
and spread the formula from Q3 to Q7

You can easily sort with =sort({P3:P7,Q3:Q7},2,false). (I made the ranges convenient for spread to query data for other columns. For example, to get column 4, which is Value Points, you can spread the formulas 1 column to the right. Hence the particular choices of absolute vs relative ranges.)

In order to use sort(), however, as you can see in the above example, you must have a prepared range. So you need to generate the results Q3:Q7, which are the sum totals, at once.

The only way to do that with native functions is pivot table or query.

We don't need to dig too deeply into how query() works. Let us only worry about how it applies in your example.

{} means local array. ; means vertical concatenation. Thus, {A3:D7;F3:I7;K3:N7} combines your blocks in 1 place. You should be able to drop the ending row number 7 for larger datasets.

Col3 and Col1 are one way you can query data columns. Since we are querying a local array, there are no letter names for the columns. Hence the use of column indices.

We want to sum column 3, Ranking Points. Hence sum(Col3). select means we are going to output the result. pivot Col1 means summing based on unique values of column 1 and it additionally means it will output both the unique values and what we selected. Moreover, the output will be a 2-by-n array where the 1st row is the unique values and 2nd row is what we selected. sort() only works with columns. Hence the need for transpose().

With that, everything is in place and we have our solution!