Splitting on Multiple Rows + Concatenation

I hope some trick is out there somewhere to handle this puzzle.

I have a column that shows which possible groups a product may belong to. The product MAY NOT belong to any group at all - but if it does; groups start after '/' and can range from A-Z.

Current format of the values is:

ProductGroup

A49-8547/A/B/C/...

X59-4857

What I need is to split this column into multiple rows so that the product code is repeated with the value of the group concatenated at the end. Note that NOT all products belong to all groups.

ProductGroup

A49-8547A

A49-8547B

A49-8547C

....

A49-8547Z

My current (very poor) approaches:

split the column on multiple rows using / as a delimiter, but cant figure out how to concatenate the group values with related product rows.
add "endswith()" filters in chronological order for testing each group (26 cascading layers). Then replacing() the remaining group names.

any help is appreciated

Preparation

Accepted answers

grazitti_sapna

HI @Shell , give this a try. I hope it works for you.

Splitting on Multiple Rows + Concatenation-GS.yxmd

All comments

JosephSerpis

Hi @Shell I mocked up an approach let me know what you think?

Split_Rows_Concatenation_27082020.yxmd

bpatel

hi @Shell ,

here is my approach. Hope this helps!

split to column.yxmd

Shell

Thank you @bpatel,

your approach is good and simple. however, it is not going to help me because you are "parsing a pattern" from the product. while indeed, this part of the value varies not only in pattern but also size. I appreciate your time.

Shell

JosephSerpis

your solution is: #simple, #effective #lovely #expandable and #logical. woks like a charm.

however, i discovered few additional patterns in my data. I'm trying to take a shot on them but would love to hear from you if you have a better idea.

here are few examples in the pattern:

ProductGroup	Comments
B34-4524/A/B/C/D	Your solution works as expected.
B34-4524A/B/C/D	notice the absent slash after number 4
B34-4524/4858/7736	In this case 4524 should be replaced by 4858 and 7736 on the following 2 rows. i.e. B34-4524 B34-4858 B34-7736

appreciate your time.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703