Creating additional row in excel based on Cell Data

[Freeman2022]

Greetings,

Ok so I'm trying to go from A to B....Need to split the data in the email address column, THEN, create additional line item.. Not sure if a formula would work, might have to try VBA...Please Help

A

Profile ID	Request Type	Change Type	Email Address
1	Notification	Yellow	Jane,Sue,Mike
2	Notification	Bule	Don,Jean,Sam,Joe

B

Profile ID	Request Type	Change Type	Email Address
1	Notification	Yellow	Jane
1	Notification	Yellow	Sue
1	Notification	Yellow	Mike
2	Notification	Bule	Don
2	Notification	Bule	Jean
2	Notification	Bule	Sam
2	Notification	Bule	Joe

 

[ExcelToDAX]

Assuming your data is in columns A1:D3, you can use this Excel function.

=DROP(REDUCE(0,SEQUENCE(COUNTA(A:A)),LAMBDA(x,y,VSTACK(x,LET(z,TEXTSPLIT(INDEX(D:D,y),,",",1),q,CHOOSEROWS(A:C,y)&"",HSTACK(IFERROR(EXPAND(q,ROWS(z)),q),TAKE(z,,1)))))),1)

Shoutout to @JvdV in this post for the motivation: Split data in to rows, new line