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.


    Thanks

  • 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