I want to find out the highest value in in cells, themselves in ranges of cells
My background is not in programming so I hope someone can help me with this problem, maybe there's a simple answer, I can do it manually, but I can't automate it.
This is a simplification, but I have bird sightings by region, by a person, in a sheet. I want to automate it so that each person's sightings in a region is given an order number, highest to lowest. i.e. the person with most sightings is given "1", the second highest "2", and so on.
At the moment I do the ordering by eye, but I make mistakes, and it's time-consuming
Example data (this data is in a sheet):
Region Person Sightings Order_No
Poole John 12 ?_(2)
Poole John_A 14 ?_(1)
Poole Chris 10 ?_(3)
Wareham John 5 ?_(3)
Wareham John_A 19 ?_(2)
Wareham Chris 21 ?_(1)
The values I want Google Sheets to calculate automatically are the values I've put in brackets. These are the values I input myself manually at the moment
The sheet is constantly being updated, with new rows being added, and existing being edited (so I might add a new row for a new person in Poole, or change the sightings for Poole/John from 12 to another value). If possible I want the order number to change, as I make changes to the data.
I've tried using Query, and VLookup, and Array formula, but unfortunately it's really hard for me to even get them to work at all. If someone can show me how to approach the problem, or solve it and show me the syntax, then that'll really help me.
paste in D2 cell and drag down:
=RANK(C2, FILTER(C:C, A:A=A2), 0)
_______________________________________________________
one-cell solution:
=ARRAYFORMULA(IFERROR(SORT(ROW(A2:A), SORT(ROW(A2:A), A2:A, 0, C2:C, 0), 1)-
MATCH(A2:A, SORT(A2:A, 1, 0), 0)))
