Transpose - Create a new column based on the name of existing columns with common prefixes

[mjh418]

Hello!

Would appreciate your help.I have source data as below.

Name	Date	ABC_Type	ABC_Quantity	ABC_Model	DEF_Type	DEF_Quantity	DEF_Model
XXX	1000	Type1	100	A	Type2	200	C
YYY	2000	Type2	200	A	Type3	200	A
ZZZ	2000	Type1	300	B	Type1	200	A

I want to make above table result in a table below in the simplest way with these traits/needs:

1. ABC, DEF columns are fixed/consitent in all source files, so I want the new column "Category" to be able to get the data derived directly from the column titles via some kind of dynamic formula. 

2. The suffixes (Type, Quantiy, Model) are also consistent for each ABC, DEFs. 

Name	Date	Category	Type	Quantity	Model
		ABC	Type1	100	A
		ABC	Type2	200	A
		ABC	Type1	300	B
		DEF	Type2	200	C
		DEF	Type3	200	A
		DEF	Type1	200	A

Thank you very much for your time and help.

[flying008]

Hi, @mjh418 

Input
Name	Date	ABC_Type	ABC_Quantity	ABC_Model	DEF_Type	DEF_Quantity	DEF_Model
XXX	1000	Type1	100	A	Type2	200	C
YYY	2000	Type2	200	A	Type3	200	A
ZZZ	2000	Type1	300	B	Type1	200	A
Output
Name	Date	Category	Model	Quantity	Type
XXX	1000	ABC	A	100	Type1
YYY	2000	ABC	A	200	Type2
ZZZ	2000	ABC	B	300	Type1
XXX	1000	DEF	C	200	Type2
YYY	2000	DEF	A	200	Type3
ZZZ	2000	DEF	A	200	Type1

[mjh418]

Thank you so much for your help, do you think I could have downloadable one? 
It is a bit difficult for me to follow the constantly moving image:(

[flying008]

Hi, @mjh418 

I can't upload any wf file, so maybe you can follow the gif or make screenshot of it for set. 

[mjh418]

Hi,

I figured it out thank you very much. I have a new question though.

Name	Date	Category	Model	Quantity	Type
XXX	1000	ABC	A,B,C	100	Type1
YYY	2000	ABC	A,C,D	200	Type2
ZZZ	2000	ABC	B,C,D	300	Type1
XXX	1000	DEF	C,D,F	200	Type2
YYY	2000	DEF	A,B,C	200	Type3
ZZZ	2000	DEF	A,D,F	200	Type1

One of the output Column, eg.g. Model above, has multiple values separated by comma. I need to have them as separate rows instead of in one cell separated by commas while keeping the same output format..any help there?

Appreciate your help.

[flying008]

Hi, @mjh418 

like this?

Name	Date	Category	Model	Quantity	Type
XXX	1000	ABC	A	100	Type1
XXX	1000	ABC	B	100	Type1
XXX	1000	ABC	C	100	Type1
YYY	2000	ABC	A	200	Type2
YYY	2000	ABC	C	200	Type2
YYY	2000	ABC	D	200	Type2
ZZZ	2000	ABC	B	300	Type1
ZZZ	2000	ABC	C	300	Type1
ZZZ	2000	ABC	D	300	Type1
XXX	1000	DEF	C	200	Type2
XXX	1000	DEF	D	200	Type2
XXX	1000	DEF	F	200	Type2
YYY	2000	DEF	A	200	Type3
YYY	2000	DEF	B	200	Type3
YYY	2000	DEF	C	200	Type3
ZZZ	2000	DEF	A	200	Type1
ZZZ	2000	DEF	D	200	Type1
ZZZ	2000	DEF	F	200	Type1