Splitting comma delimted excel cell into multiple rows based on certain criteria

Hi Everyone,
I have attached an excel file which contains two sheets "Before" and "After" . I want to make change the data in "Before" sheet to "After" sheet. The data in excel column B which is comma delimited should be splitted into multiple rows and the data in column A should repeat itself accordingly. When you will open the file then you will easily understand it. Many thanks in advance for your help.

Best Regards,

Please try at sheet "Before"

D1

=IFERROR(INDEX($A$1,(ROWS(B$1:B1)+LEN(B$1)-LEN(SUBSTITUTE(B$1,",","")))/(LEN(B$1)-LEN(SUBSTITUTE(B$1,",",""))+1)),"")

E1

=IFERROR(--MID(SUBSTITUTE(B$1,",",REPT(" ",LEN(B$1))),1+(ROWS(B$1:B1)-1)*LEN(B$1),LEN(B$1)),"")

Thanks for your reply. but here is a situation. The formulas have limitations that if there is similar data in column A and B in multiple rows then it won't work.In otherwords, the formulas you provided works for only row 1. I want a such formulas which could work for multiple rows.

Please see attached

Why is there some data in column C and D and the same columns have been used in formulas as well. Though the formulas are working fine but I am confused about column C and D.

C is for count comma +1
D is helper column base on C

D is needed for guide the G# so from G1-G4 is with A1 "a" and G5-G10 stay with A2 "B" and so on.

Or……………………………..

Try this formula solution without helper

1] Using FlameRetired's layout table

2] In D1, copied down :

=IFERROR(INDEX(A$1:A$3,AGGREGATE(15,6,ROW(A$1:A$3)/(LEN(B$1:B$3)-LEN(SUBSTITUTE(B$1:B$3,",",""))+1>=COLUMN($A:$J)),ROWS($1:1))),"")

3] In E1, copied down :

=IF(D1="","",TRIM(MID(SUBSTITUTE(","&VLOOKUP(D1,A$1:B$3,2,0),",",REPT(" ",99)),COUNTIF(D$1:D1,D1)*99,99)))

Regards
Bosco