Split sequences in multiple rows

Hello everyone,
I have sequences of data stored in different columns. Since my network diagrams can only take sequences with max length of 2 I need to split longer sequences.
In my Input table you see my first sequence: AGFA (splitted in multiple columns) that I need to manipulate in a way that the max length is only allowed to be 2 (see first output table)

After archiving the first step some entries occure two times. e.g. F originally occurs one time in the sequence but with this manipulation it occurs two times (in GF and FB). Therefore I need a way to double all the entries, that are not doubled yet so I can divide the sum by 2 in my diagram later. (see next Step (double entries) table)

In the last step I need to remove all entries in the column “Sequence” except for one time PER Topic (Same sequences can occur multiple times in different topics). So I can just count the times of occurence per whole Sequence.

I know its a lot but I hope someone can help me with this :smiley:

Thank you in advance!

Not really sure, but is this something like that ?
test.knwf (53.4 KB)

thank you for your answer. This looks almost perfect, I will try to adapt this to my real data! I have just one more small thing: I want to archive that the sequence (Column3) should only appear once per topic (see last table I uploaded in the picture). But this already helps so much, thank you!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.