Sequential serial numbers do not come up in sequence

I have a number of areas for whom I raise Penalty Notices, and want them to show in sequntial order. When i trip over 9 into 10, it then sequences after the #1 number, and not after the #9 ...

Excel on Mac

PeterBartholomew1

Silver Contributor

Nov 02, 2023

Phil_Gibbs

A formula that could prove useful is

= LET(
    seq,   VALUE(MID(areaCode, 5, 2)),
    area,  LEFT(areaCode, 4),
    area & TEXT(seq, "00")
  )

That would create a calculated list of area codes of the form FORW02 (as suggested by Riny_van_Eekelen ) which could either be used in dependent formulas or to Copy / Paste values over the originals.

Equally, the formula

= LET(
    seq,  VALUE(MID(areaCode, 5, 2)),
    area, LEFT(areaCode, 4),
    SORTBY(records, area, 1, seq, 1)
  )

would create an output that is sorted on the sequence digits as numbers.

Phil_Gibbs
Copper Contributor
Nov 02, 2023
all sorted, thank you

Forum Discussion

Sequential serial numbers do not come up in sequence

Resources