CODEWITHSUNDEEP
excelexcel-formula
Arunraj Chandran
Arunraj Chandran

Reputation: 181

Row number and partition by in MS excel

I have data in excel such as:

ID        | Fee
123456789 | 100
987654321 | 100
987654321 | 100
987654321 | 50

What I do not know how to do is use a similar row_number() over(partition by) in excel that I would normally do in SQL

Partition by (ID and Fee)

Desired output would be

ID        | Fee   | rn
123456789 | 100   | 1
987654321 | 100   | 1
987654321 | 100   | 2
987654321 | 50    | 1

Upvotes: 0

Views: 2961

Answers (2)

JvdV
JvdV

Reputation: 75860

In C1 you could use:

=IF(AND(A2=A1,B2=B1),C1+1,1)

Upvotes: 1

P.b
P.b

Reputation: 11483

If ID is column A and fee is column B then use the following formula in C2: =SUMPRODUCT(--($A$2:$A2&$B$2:$B2=A2&B2))

This also works if the data is not in order.

Upvotes: 1

Related Questions