3

I have some data in a Google Sheets document that I would like to sort by computing some value from two columns and using that as the sort key.

Say my data is like this:

A B C D
1 ... ... 8700 5
2 ... ... 1 32000
3 ... ... 0 4200
4 ... ... 2500 7000

I want to sort the data in decending order based on max(C, D) so that the data looks like this:

A B C D
1 ... ... 1 32000
2 ... ... 8700 5
3 ... ... 2500 7000
4 ... ... 0 4200

I was able to do it with a Python script like this, but that required exporting the sheet and then re-importing it.

import csv

with open('source_sheet.csv', newline='') as in_file, open('output.csv', 'w', newline='') as out_file:
    reader = csv.DictReader(in_file)
    sorted_data = sorted(reader, key=lambda x: max(int(x['C']), int(x['D']), reverse=True)
    writer = csv.DictWriter(out_file, fieldnames=reader.fieldnames)
    writer.writeheader()
    writer.writerows(sorted_data)

Is there some formula or function that can do what I want?

1

1 Answer 1

2

You can achieve the desired result using built-in functions like this:

=sort(A1:D4,byrow(C1:D4,lambda(row,max(row))),0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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