Forum Discussion

Phil_Gibbs's avatar
Phil_Gibbs
Copper Contributor
Nov 02, 2023

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 number.  How do i get the sequence to continue??

Excel on Mac
  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Nov 02, 2023

    Phil_Gibbs 

    If with sql,

    //select * from basic_order_by_natural;
    select * from basic_order_by_natural order by number COLLATE NATURAL_CMP ;

     

     

  • PeterBartholomew1's avatar
    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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 02, 2023

    Phil_Gibbs That's because you are sorting texts. "FORW11" comes before "FORW2".

     

    I you want the text sorting to be correct, change the codes so that the have the same number of numbers at the end using leading zeroes. Then "FORW002" will sort before "FORW011".

Resources