Sorting column based on the cell value condition

[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

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#

[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

[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.

Input

AA 02  AAA  ABCD3

90 01  ABC1+ABC2=ABCD3#

AA 02  BBB  ABCEF

90 01  101A=ABCEF#

AA 02  CCC  ASDF9

90 01  ASDF1+ASDF2+ASDF3+

90 02  ASDF4+ASDF5=ASDF9#

AA 02  DDD  ASDF4

90 01  201Z=ASDF4#

Output

AA 02  BBB  ABCEF

90 01  101A=ABCEF#

AA 02  DDD  ASDF4

90 01  201Z=ASDF4#

AA 02  AAA  ABCD3

90 01  ABC1+ABC2=ABCD3#

AA 02  CCC  ASDF9

90 01  ASDF1+ASDF2+ASDF3+

90 02  ASDF4+ASDF5=ASDF9#

[NikyN]

Hello @Gsiva3,

I believe that for 90 01  ASDF1+ASDF2+ASDF3+ is still something missing on the end to match if with ASDF9#.

Best Luck!

Niky

[Gsiva3]

Hi @NikyN 

90 01  ASDF1+ASDF2+ASDF3+ has a continuous line below as 90 02  ASDF4+ASDF5=ASDF9# were you can find the string ASDF9.

I hope this might help you.

[NikyN]

Hello @Gsiva3,

I am afraid that I cannot help on this one -- I cannot see any connection/pattern between 90 01 and 90 02.

Best Luck!

Niky

[Gsiva3]

Hi @NikyN 

Thanks for the support

Hi all, is there anyone who can support me on this?