0

I have a flag in a table. When I am trying to sort using Unique formula. It is not sorted as per my requirement.

Flag order needs to be

Flag
T1
T2
T10
T11

instead of

Flag
T1
T10
T11
T2
T3

Below is my formula, I am also excluding DS and MS flag.

=SORT(UNIQUE(filter(A:A,A:A<>"DS",A:A<>"MS")))
2
1

To sort the flags primarily in alphabetic order by their alpha prefix, and secondarily in numeric order by the numbers that follow the alpha prefix, while omitting duplicate values and values where the prefix is DS or MS, and also omitting blanks, use this:

=query( 
  sortn( 
    A2:A, 
    9^9, 
    2, 
    iferror(regexextract(A2:A, "^[^\d]+"))
    & 
    iferror(text(value(regexextract(A2:A, "\d+")), "000000")), 
    true 
  ), 
  "where Col1 is not null 
   and not lower(Col1) matches '^(ds|ms)' 
   label Col1 'Flag' ", 
  0 
)

See cell L2 in your sample spreadsheet.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.