Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    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!
    George
    Home | Blog

  2. #2
    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. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •