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

Question

Hello!

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

NameDateABC_TypeABC_QuantityABC_ModelDEF_TypeDEF_QuantityDEF_ModelXXX1000Type1100AType2200CYYY2000Type2200AType3200AZZZ2000Type1300BType1200A

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.

NameDateCategoryTypeQuantityModel  ABC

Type1100A  ABCType2200A  ABCType1300B  DEFType2200C  DEFType3200A  DEFType1200A

Thank you very much for your time and help.

flying008 · Answer

Hi, @mjh418

like this?

NameDateCategoryModelQuantityTypeXXX1000ABCA100Type1XXX1000ABCB100Type1XXX1000ABCC100Type1YYY2000ABCA200Type2YYY2000ABCC200Type2YYY2000ABCD200Type2ZZZ2000ABCB300Type1ZZZ2000ABCC300Type1ZZZ2000ABCD300Type1XXX1000DEFC200Type2XXX1000DEFD200Type2XXX1000DEFF200Type2YYY2000DEFA200Type3YYY2000DEFB200Type3YYY2000DEFC200Type3ZZZ2000DEFA200Type1ZZZ2000DEFD200Type1ZZZ2000DEFF200Type1

mjh418 · Answer

Hi,

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

NameDateCategoryModelQuantityType  XXX1000ABCA,B,C100Type1  YYY2000ABCA,C,D200Type2  ZZZ2000ABCB,C,D300Type1  XXX1000DEFC,D,F200Type2  YYY2000DEFA,B,C200Type3  ZZZ2000DEFA,D,F200Type1

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 · Answer

Hi, @mjh418

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