Auto numbering based on values from an adjacent cell

Hi Community,

I have the below sheet:

The value in the "State Code" column should take the State Abbreviation and add the next number in the sequence of that particular State.

Anyone know what the formula would be to enable this?

Many thanks,

Ahmet

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You will want to insert an Auto/number column (don't need any special formatting) then use this formula in a text/number column:

    =States@row + "-" + IF(COUNTIFS(States:States, @cell = States@row, AutoNumber:AutoNumber, @cell <= AutoNumber@row) < 10, "00", IF(COUNTIFS(States:States, @cell = States@row, AutoNumber:AutoNumber, @cell <= AutoNumber@row) < 100, "0")) + COUNTIFS(States:States, @cell = States@row, AutoNumber:AutoNumber, @cell <= AutoNumber@row)