Number a list based on conditions, with non consecutive data sets

  • I have a big set of data that will grow over time.

    I need a formula that that will do the following:

    I need to add an incremental set of numbers starting at 1 and adding 1 every time column B changes set.

    On top of this I need to restart the numbering every time column I changes.

    But crucially, if either column B or column I matches a previous value I need to add the same number again. And if it is a new number in the sequence then add that...

    Difficult to explain in words but I have attached a sample work book that hopefully makes it clear - the number in column J is what I need to replace with a formula.


  • Hi there, thanks for the reply,

    That's nearly there, but I also need, at row 40 you will see the variable in column I changes, so I need col j to start at 1 again, but on row56, col B reverts to the first data name and that has appeared before, so at that point I need it to put the corresponding previous value in column J..

    In other words, Column B value has appeared previously, in which case the number in J should match the number from when B appeared previously.

    If the value in column B has not appeared in the list previously, then it should add +1 to the highest value that has appeared against the corresponding column I.

    I have added a column of numbers in column L to show the numbers I need the formula to create.

  • Hi again,

    Attached is your Version 2

    Formula in Column J has been modified to reflect the results shown in Column L ...

    However there is one row : Row 64 for which cannot understand how the number 6 is calculated ...

  • Awesome thanks,

    I was just nearly there with a helper column with a nested CountIFS function and then a MaxIFS function.... but this is much neater.

    row 64... that must have been my deliberate error... well done for spotting it ;)

    Thanks again