Auto numbering based on values from an adjacent cell

Ahmet - newby

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

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)