Multi-Field Text to Columns (Rows)

[EricDM]

Joe,
 
I have a similar issue and have attempted to use your workflow as a starting point, but am failing to get the output that I need.  Can you assist?
This is the data as it is provided to me (There are many additional rows and columns that I have cut out to simplify):
 
Pipes delineate the Control entries
ID
Control1
Control2
Control3
Control4
Control5
Control6
Control7
Control8
OtherData1
OtherData2

AB-1
12|DD|AC
 
LL|BMI|CR|BC|BB
LCA
B55
KMG64
LRE|LRD|LRF|LRG
S|T|A|L|W|Q
This is a
Number of
DC-1
 
123|213|312
 
NAB|BAM
RED|REM
 
KWW|KZL|KQQ|LLM
CD1|CD2
Went Left
Correct
XX-3
AQ
SS
ZAZ
LBZ
ZAD
PP
B204|B211|B300
J11|J006
Adjacent
Marko
 
And this would be the desired output:
ID
Control1
Control1
Control1
Control2
Control3
Control3
Control3
Control3
Control3
Control4
Control5
Control6
Control7
Control7
Control7
Control7
Control8
Control8
Control8
Control8
Control8
Control8
OtherData1
OtherData2
AB-1
12
 DD
 AC
 
 LL
BMI 
CR 
BC 
BB  
LCA
B55
KMG64
LRE
LRD
LFR
LRG
S
T
A
L
W
Q
This is a
Number of
Etc...
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
So instead of creating a RecordID, I need to go off the ID.
 
Thanks in advance,
 
Eric

[Joe_Mako]

How about the attached?

(Edited)

[EricDM]

Needed to update the output requirement.

[EricDM]

Sometimes, life is redonkulous...

Output has changed again.

This is the way the data comes to me.  Pipes delineate the Control entries

ID	Control1	Control2	Control3	Control4	Control5	Control6	Control7	Control8	OtherData1	OtherData2
AB-1	12|DD|AC		LL|BMI|CR|BC|BB	LCA	B55	KMG64	LRE|LRD|LRF|LRG	S|T|A|L|W|Q	This is a	Number of
DC-1		123|213|312		NAB|BAM	RED|REM		KWW|KZL|KQQ|LLM	CD1|CD2	Went Left	Correct
XX-3	AQ	SS	ZAZ	LBZ	ZAD	PP	B204|B211|B300	J11|J006	Adjacent	Marko

This is how I have been asked to provide it.  If it changes again, I might consider working the fry machine at McDonalds.  :-)

Notice now that they would like the controls grouped into their own heading and another column for the details...

ID	Control	Control Detail	OtherData1	OtherData2
AB-1	Control1	12	This is a	Number of
AB-1	Control1	DD	This is a	Number of
AB-1	Control1	AC	This is a	Number of
AB-1	Control2		This is a	Number of
AB-1	Control3	LL	This is a	Number of
AB-1	Control3	BMI	This is a	Number of
AB-1	Control3	CR	This is a	Number of
AB-1	Control3	BC	This is a	Number of
AB-1	Control3	BB	This is a	Number of
AB-1	Control4	LCA	This is a	Number of
AB-1	Control5	B55	This is a	Number of
AB-1	Control6	KMG64	This is a	Number of
AB-1	Control7	LRE	This is a	Number of
AB-1	Control7	LRD	This is a	Number of
AB-1	Control7	LRF	This is a	Number of
AB-1	Control7	LRG	This is a	Number of
AB-1	Control8	S	This is a	Number of
AB-1	Control8	T	This is a	Number of
AB-1	Control8	A	This is a	Number of
AB-1	Control8	L	This is a	Number of
AB-1	Control8	E	This is a	Number of
AB-1	Control8	Q	This is a	Number of

I have a workflow developed, but I am unable to get the Control Details column to output correctly and it appears that the Control column is not plotting in the correct order, even going off of a Record ID that has been inserted.

Joe_Mako has been a life saver!!  I like that he includes example workflows so that I can learn what each tool is actually doing.

[EricDM]

I have successfully confused myself after receiving several output requests.  I am now being asked to create a new column that will contain the Controls and another that will contain the details.  But I still need the sort on the ID and include the other data fields.

Here is how the data is delivered.  Pipes delineate the Control entries

ID	Control1	Control2	Control3	Control4	Control5	Control6	Control7	Control8	OtherData1	OtherData2
AB-1	12|DD|AC		LL|BMI|CR|BC|BB	LCA	B55	KMG64	LRE|LRD|LRF|LRG	S|T|A|L|W|Q	This is a	Number of
DC-1		123|213|312		NAB|BAM	RED|REM		KWW|KZL|KQQ|LLM	CD1|CD2	Went Left	Correct
XX-3	AQ	SS	ZAZ	LBZ	ZAD	PP	B204|B211|B300	J11|J006	Adjacent	Marko

And this would be the desired output:

ID	Control Name	Control Details	OtherData1	OtherData2
AB-1	Control1	12	This is a	Number of
AB-1	Control1	DD	This is a	Number of
AB-1	Control1	AC	This is a	Number of
AB-1	Control2		This is a	Number of
AB-1	Control3	LL	This is a	Number of
AB-1	Control3	BMI	This is a	Number of
AB-1	Control3	CR	This is a	Number of
AB-1	Control3	BC	This is a	Number of
AB-1	Control3	BB	This is a	Number of
AB-1	Control4	LCA	This is a	Number of
AB-1	Control5	B55	This is a	Number of
AB-1	Control6	KMG64	This is a	Number of
AB-1	Control7	LRE	This is a	Number of
AB-1	Control7	LRD	This is a	Number of
AB-1	Control7	LRF	This is a	Number of
AB-1	Control7	LRG	This is a	Number of
Etc...

Joe_Mako is a life saver...learning more from him in the past few days that I have in the last few weeks on my own.

[Joe_Mako · Accepted solution]

[rohanonline]

You can try this as well