# Thread: Row_Number() OVER (PARTITION BY.... In Excel!

1. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445

## Unanswered: Row_Number() OVER (PARTITION BY.... In Excel!

So I had the fun job of trying to reproduce functionality similar to Row_Number() from SQL, in Excel, the other day and thought I would share my solution.

Basically, my challenge was to have a row counter against every record that reset on change of a certain field.

For example: employee absence dates. First absence = 1, second absence = 2, etc. Should reset to 1 for each employee!

Assuming that the employee number is in column A
and that date of absence is in column B

Number of records per employee:
Code:
`=CountIfs(A:A, A1)`
Row number per employee:
Code:
`=CountIfs(A:A, A1, B:B, "<=" & B1)`
Note that to work correctly it is assumed that the combination of employee number and date of absence is unique.

Hope this helps someone in the future!

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
In terms of calculation hungriness, I'd generally label COUNTIFS() in the medium to high range.

The key to getting the a more efficient solution is to have the data sorted first by Employee number and then Date of Absence (ASC).
Then, assuming the data has headers in row 1, the formula in C2 (and then fill down the column) to get the row_number is: =IF(A2=A1,C1+1,1)

This also assumes Emp No/Absence is unique.

3. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Very good points, Colin!

Unfortunately I can't trust the end users that this was put together for, with having data in the right order
I have also developed my solution to work with Excel Tables, and to implement your method would require the use of Offset(), which I have found a little greedy too.