Sorting column based on the cell value condition

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

AA 02 AAA

90 01 ABC1+ABC2=ABC3#

AA 02 BBB

90 01 101A=ABC1#

AA 02 CCC

90 01 ASDF1+ASDF2+ASDF3+

90 02 ASDF4+ASDF5=ASDF9#

AA02 DDD

90 01 201Z=ASDF4#

Output

AA 02 BBB

90 01 101A=ABC1#

AA 02 DDD

90 01 201Z=ASDF4#

AA 02 AAA

90 01 ABC1+ABC2=ABC3#

AA 02 CCC

90 01 ASDF1+ASDF2+ASDF3+

90 02 ASDF4+ASDF5=ASDF9#

Datasets

Data Investigation

Accepted answers

All comments

NikyN

Hello @Gsiva3,

Please find attached solution -- let me know if it works for you!

The problem is with AA02 DDD as there is not space between AA and 02 -- I add space manually, but in case that you might have such a cases in you dataset -- in that case after 'Text to Column' tool you will need to add formula.

Hope it will works for you!

Best Luck!

Niky

Sorting column based on the cell value condition.yxmd

Gsiva3

Hi Niky,

I am getting it partially.

What I actually want is I want both the AA line & 90 line to be sorted as a pair.

But in the workflow, 90 line is getting sorted correctly, but the AA line which is above the 90 line is not getting reflected correctly.

Attached desired output below.

Output

AA 02 BBB

90 01 101A=ABC1#

AA 02 DDD

90 01 201Z=ASDF4#

AA 02 AAA

90 01 ABC1+ABC2=ABC3#

AA 02 CCC

90 01 ASDF1+ASDF2+ASDF3+

90 02 ASDF4+ASDF5=ASDF9#

NikyN

Hello @Gsiva3,

I see -- do you know based on what logic AA 02 BBB is pared with 90 01 101A=ABC1# etc.

Unfortunately, I cannot see any pattern here. I did not realized that AAA is in the middle and not on the top.

If there is any logic behind, we can implement it into Alteryx --- otherwise it will be hardcoded.

Thank you!

Best Luck!

Niky

Gsiva3

Got it.

Sorry forgot to add one thing in the input. There is a connection between AA & 90 Line, In AA line at the end there is a string which is available in end of the 90 line with "#". I think this might help you.