0

I am trying to write an array formula that ranks Column A and partitions the rank by columns B and C but I can't work out how to do it. Essentially I want RANK() OVER (PARTITION BY B,C ORDER BY A DESC).

I currently have the array formula below in column D that works for ranking Col A and partitioning by Col B, but I can't work out how to make the formula partition by B and C:

=ArrayFormula(if(B2:B<>"",vlookup(B2:B&"|"&A2:A,{sort(B2:B&"|"&A2:A,B2:B,true,A2:A,false),row(B2:B)},2,false)-vlookup(B2:B,{sort(B2:B,B2:B,true),row(B2:B)},2,false)+1,""))

I would like to rank the each person on each activity (in Column E). Column D shows the formula I have which only gets the most recent time for each person.

enter image description here

E.B
  • 1
  • 3
  • 1
    can you share a[n] (editable) sample sheet? – MattKing Mar 24 '20 at 15:56
  • share a copy of your sheet with example of desired output – player0 Mar 24 '20 at 16:59
  • I have added a sheet now – E.B Mar 26 '20 at 08:45
  • You should be aware that sharing your spreadsheet will unavoidably expose your email address. – Tedinoz Mar 26 '20 at 10:38
  • I'm trying to make your function work but i'm not being able. I reproduced the sheet with data as you have it in the image and trying to use the function you're using in column D but it's throwing a parse error. – Andres Duarte Apr 01 '20 at 08:18
  • Apologies, formula edited to `=ArrayFormula(if(B2:B<>"",vlookup(B2:B&"|"&A2:A,{sort(B2:B&"|"&A2:A,B2:B,true,A2:A,false),row(B2:B)},2,false)-vlookup(B2:B,{sort(B2:B,B2:B,true),row(B2:B)},2,false)+1,""))` – E.B Apr 02 '20 at 09:16

1 Answers1

0

You can use the following formula in the D2 cell and then drag it down to copy it in the below cells:

=Rank(A2,Filter(A$2:$A,B$2:$B=B2, C$2:$C=C2),1)

This would use rank and filter functions. I didn't manage to find a solution with array formula.

EDIT

Using ArrayFormula, the closest I got was using this formula:

=ArrayFormula(rank(A2:A34,QUERY(A2:C34, "select A where B ='"&B2&"'and C ='"&C2&"'", -1)))

The problem is that will only work for ranking the values that have the same B and C values from the first row, the idea was to use the value from each row cell instead of hardcoding B2 and C2, but for conditions in query or filter functions the value remains the same for each row (i.e. using row() function) which is the one where the arrayformula function is inserted (second row in this case). You would need to develop, in my opinion, a complicated algorithm with vlookup function as you were doing before.

Andres Duarte
  • 3,166
  • 1
  • 7
  • 14
  • Thanks, I know I can use FILTER and drag it down, however the lines are added by google form (So new rows are inserted into the sheet). My current solution is to use a google script to input this formula 'on form submit', however I would very much prefer an array formula – E.B Apr 04 '20 at 19:02