How to sort based on cell values

[Gsiva3]

Hi Team, I was just wondering on how to sort the column data based on the condition of the cell values.

I have a input with column A, where two line formats are available. One line always starts with AA & another line always starts with 90. The line that starts with AA contains normal text whereas the line that starts with 90 contains coding and the 90 line can be maximum of 30 lines in a continuous number format like 90 01,90 02, 90 03,etc. But coding in 90 01,90 02, 90 03 refers to be continuous coding.

My concern here is line that starts with 90, always end with an "#" symbol. For example "90 01 AQWE1+AQWE2=AQWR4#"

Hence, the code which is before this "#" symbol in the above mentioned example  is AQWR4 which should not be available in any of the previous 90 lines which are in the input. If this AQWR4 is deployed in any of the previous 90 line then that 90 line & the respective AA line should come below this AQWR4# line. This is how I want to get it sorted.

I have mentioned the input & expected output below. Pease help me on this.

Input	Output
AA 02 ZAD BVCZ9	AA 02  BBB  AVCDE
90 01 AVCDE+BCZA2=BVCZ9#	90 01  101A=AVDCE#
AA 02  AAA  ABCD3	AA02  DDD  QWERS
90 01  ASDF9+ABCD2=ABCD3#	90 01  201Z=QWERS
AA 02  BBB  AVCDE	AA 02  CCC  ASDF9
90 01  101A=AVDCE#	90 01  ASDF1+ASDF2+ASDF3+
AA 02  CCC  ASDF9	90 02  QWERS+ASDF5=ASDF9#
90 01  ASDF1+ASDF2+ASDF3+	AA 02  AAA  ABCD3
90 02  ASDF4+ASDF5=ASDF9#	90 01  ASDF9+ABCD2=ABCD3#
AA02  DDD  QWERS	AA 02 ZAD BVCZ9
90 01  201Z=QWERS#	90 01 AVCDE+BCZA2=BVCZ9#

[KilianL]

Hi @Gsiva3 ,

I can think of the following approach:

1. identify the Code for each line, depending on AA or 90 -> formula tool with an If-Else for AA/90 and simple RegEx for the Code

2. sort on the code and then sort on the line itself -> sort tool

You can find the example attached.

Here is the formula code used:

You can avoid the RegEx if the code is always 5 characters long and at the end of each line. This could be done with a simple right([Input],5) instead instead of the Regex_Replace function.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx