• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rank based on values in 2 colums

ssj011

New Member
Good Day,

Need the formula to rank the data based on the 2 columns

  1. Rank should be for a combination of values in 2 different columns
  2. Rank should be ascending order till the value in the D is not changed
  3. If the value in the D column changes the rank should start again from 1

Regards
Sachin
 

Attachments

  • Book2(AutoRecovered).xlsx
    12.2 KB · Views: 10
ssj011
Rank
returns the rank of a number in a list of numbers.
...
Your shown C-column results (without those numbers) could get like this sample without any formulas.
 

Attachments

  • Book2(AutoRecovered).xlsx
    16.9 KB · Views: 3
Please try at
F1
=IF(D2=D1,IF(E2=E1,"",LOOKUP(9^9,F$1:F1)+1),1)

or MS365 Spill Array
=LET(a,D2:E86,b,UNIQUE(a),c,INDEX(a,,1)&INDEX(a,,2),
d,SEQUENCE(ROWS(b))-MATCH(INDEX(b,,1),INDEX(b,,1),)+1,
IF(MATCH(c,c,)=SEQUENCE(ROWS(a)),XLOOKUP(c,INDEX(b,,1)&INDEX(b,,2),d),""))


74159
 

Attachments

  • Book2(AutoRecovered).xlsx
    15.3 KB · Views: 1
Back
Top