0

I have a table like below where I need to group by column A and determine rank/priority based on the logic below:

These are the steps I would like to do :

  • First Group by column A
  • Then check column B for each group. The lower the start date, the smaller the rank for example if the dates are 2022 and 2023, 2022 will get rank 1 and 2023 will get rank 2 and so on ...
  • If the start years are the same for rows, then check column C and assign rank based on the Impact. Higher the impact, lower the rank. For example if 2 rows have start year 2022 then if impact is 100 for one row and 50 for the other, impact with 100 will get rank 1 and 50 will get rank 2 and so on ...

Please see picture for sample data and expected output in column D

I have tried this function but it only gives me rank based on one column - https://infoinspired.com/google-docs/spreadsheet/rank-group-wise-in-google-sheets/

Also clarifying that Column D is what I would like to generate through the formula. I was just showing that as expected output in the image. We don't have that available and need to generate that rank from columns A, B and C!

Any ideas/thoughts are appreciated! Thank you!

Varsha Venkatraman
  • 1
  • 1
  • 1
    Please , include your sheet take a look at [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Osm Jul 20 '22 at 21:47
  • 1
    the lower the year that gets the higher rank. OR The lower the start date, the smaller the rank ? – Osm Jul 21 '22 at 05:02

2 Answers2

0

You can use the following

=SORT(A2:D13,1,1,2,1,3,0)

(Do adjust the formulae according to your ranges and locale)

enter image description here

Read about SORT

marikamitsos
  • 10,264
  • 20
  • 26
  • Hi sorry I should have been clear. Column D is what I would like to generate through the formula. I was just showing that as expected output. We don't have that available and need to generate that rank from columns A, B and C! – Varsha Venkatraman Jul 20 '22 at 23:36
  • I bet you didn't even try the formula @VarshaVenkatraman . As mentioned: *(**Do** adjust the formulae according to your ranges and locale)* . Then, give it a try. Also. Please read about [SORT](https://support.google.com/docs/answer/3093150) – marikamitsos Jul 21 '22 at 04:10
  • 1
    I did try it but in your formula you reference column D. I do not actually have column D in my table. That is the column that needs to be generated. It is not about just sorting the values, I need to generate the rank in column D based on columns A, B and C and the logic outlined in the question. Thank you! – Varsha Venkatraman Jul 21 '22 at 04:33
0

try:

=ARRAYFORMULA(IFERROR(1/(1/VLOOKUP(A2:A&B2:B&C2:C, 
 {SORT(A2:A&B2:B&C2:C, A2:A, 1, B2:B, 1, C2:C, 0), 
 COUNTIFS(SORT(A2:A), SORT(A2:A), ROW(A2:A), "<="&ROW(A2:A))}, 2, 0))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124