Concat string & create new row

[123love55448]

I want to search for "Fat" in each row then creating new role for strings that come after "Fat" (if any) by maintaining inputs in other columns the same

Input file:

Name	Date	List of actions
Mister A	9/6/2022	Bat, Hat, Rat, Fat, Hat, Rat, Oat
Mister A	10/12/2022	Rat, Oat, Rat, Fat, That, Rat
Mister B	2/8/2022	Bat, Oat, Rat
Mister C	4/5/2022	That, Rat, Fat, Bat, Bat, Hat, Eat

Output:

Name	Date	List of actions
Mister A	9/6/2022	Bat, Hat, Rat, Fat
Mister A	9/6/2022	Hat, Rat, Oat
Mister A	10/12/2022	Rat, Oat, Rat, Fat
Mister A	10/12/2022	That, Rat
Mister B	2/8/2022	Bat, Oat, Rat
Mister C	4/5/2022	That, Rat, Fat
Mister C	4/5/2022	Bat, Bat, Hat, Eat

If there is more than one Fat in input row, keep splitting until no "Fat" left in ending row

Appreciated all the helps

[IraWatt]

Hey @123love55448,

Are you wanting to keep the original row like you did with "Bat, Hat, Rat, Fat, Hat, Rat, Oat" or not keep the original row like with "That, Rat, Fat, Bat, Bat, Hat, Eat"?

[IraWatt]

If you are wanting to keep the original row here is one way:

I split each row to individual actions and use a multirow formula to generate the new text for each row.

If you want to learn more about the Multi-Row-Formula Tool the community has some quick and easy videos on the topic here: https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[JosephSerpis]

Hi @123love55448 I came up with an approach of using a formula to replace fat, with fat| so I could use | as delimiter in the Text to columns tool to achieve the output you stated.

[grazitti_sapna]

@123love55448 , my solution is similar to @JosephSerpis , you can replace the text Fat, with Fat| or some other delimiter instead of , and then split the data to rows using text to columns tool on the basis of |. Attaching the solution hope it helps.