textsplit to next row with the same value in parallel row

[ExcelNewbie2020]

how to textsplit column B by rows while keeping the value in column A?..thank you

Book1
	A	B	C	D	E
1	NAME1	APPLE, BERRY, DOG, CAT		EXPECTED RESULT
2	NAME2	APPLE, BERRY		NAME1	APPLE
3	NAME3	APPLE, BERRY, CAT		NAME1	BERRY
4	NAME4	BERRY, DOG, CAT		NAME1	DOG
5	NAME5	APPLE, DOG, CAT		NAME1	CAT
6				NAME2	APPLE
7				NAME2	BERRY
8				NAME3	APPLE
9				NAME3	BERRY
10				NAME3	CAT
11				NAME4	BERRY
12				NAME4	DOG
13				NAME4	CAT
14				NAME5	APPLE
15				NAME5	DOG
16				NAME5	CAT
17
18
19
20
21
Sheet3

 

[Kerryx]

Power query

Book1
	A	B	C	D	E	F	G	H
1	Column1	Column2		EXPECTED RESULT
2	NAME1	APPLE, BERRY, DOG, CAT		NAME1	APPLE		Column1	Value
3	NAME2	APPLE, BERRY		NAME1	BERRY		NAME1	APPLE
4	NAME3	APPLE, BERRY, CAT		NAME1	DOG		NAME1	BERRY
5	NAME4	BERRY, DOG, CAT		NAME1	CAT		NAME1	DOG
6	NAME5	APPLE, DOG, CAT		NAME2	APPLE		NAME1	CAT
7				NAME2	BERRY		NAME2	APPLE
8				NAME3	APPLE		NAME2	BERRY
9				NAME3	BERRY		NAME3	APPLE
10				NAME3	CAT		NAME3	BERRY
11				NAME4	BERRY		NAME3	CAT
12				NAME4	DOG		NAME4	BERRY
13				NAME4	CAT		NAME4	DOG
14				NAME5	APPLE		NAME4	CAT
15				NAME5	DOG		NAME5	APPLE
16				NAME5	CAT		NAME5	DOG
17							NAME5	CAT
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}, {"Column2.4", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Column2.1", "Column2.2", "Column2.3", "Column2.4"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

 

[ExcelNewbie2020]

Power query

Book1
	A	B	C	D	E	F	G	H
1	Column1	Column2		EXPECTED RESULT
2	NAME1	APPLE, BERRY, DOG, CAT		NAME1	APPLE		Column1	Value
3	NAME2	APPLE, BERRY		NAME1	BERRY		NAME1	APPLE
4	NAME3	APPLE, BERRY, CAT		NAME1	DOG		NAME1	BERRY
5	NAME4	BERRY, DOG, CAT		NAME1	CAT		NAME1	DOG
6	NAME5	APPLE, DOG, CAT		NAME2	APPLE		NAME1	CAT
7				NAME2	BERRY		NAME2	APPLE
8				NAME3	APPLE		NAME2	BERRY
9				NAME3	BERRY		NAME3	APPLE
10				NAME3	CAT		NAME3	BERRY
11				NAME4	BERRY		NAME3	CAT
12				NAME4	DOG		NAME4	BERRY
13				NAME4	CAT		NAME4	DOG
14				NAME5	APPLE		NAME4	CAT
15				NAME5	DOG		NAME5	APPLE
16				NAME5	CAT		NAME5	DOG
17							NAME5	CAT
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2", "Column2.3", "Column2.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}, {"Column2.4", type text}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type1", {"Column2.1", "Column2.2", "Column2.3", "Column2.4"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Only Selected Columns",{"Attribute"})
in
    #"Removed Columns"

thanks mate, but I'm not familiar with power query. I hope its possible using excel formula..

 

[ISY]

Hi

=LET(X,A1:A10,Y,B1:B10,k,TOCOL(BYROW(Y,LAMBDA(s,ROWS(TEXTSPLIT(s,,", ")))),3),CHOOSE({1\2},TOCOL(IF(SEQUENCE(,MAX(k))<=k,X,W),3),FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,SUBSTITUTE(Y,", ","</s><s>"))&"</s></t>","//s")))

 

[mohadin]

Hi
VBA
Try

Sub test()
    Dim a, x
    Dim i&, ii&
    a = Cells(1).CurrentRegion
    For i = 1 To UBound(a)
        x = Split(a(i, 2), ",")
        Cells(2 + ii, 4).Resize(UBound(x) + 1) = Cells(i, 1)
        Cells(2 + ii, 5).Resize(UBound(x) + 1) = Application.Transpose(x)
        ii = ii + UBound(x) + 1
    Next
End Sub

 

[ExcelNewbie2020]

Hi

=LET(X,A1:A10,Y,B1:B10,k,TOCOL(BYROW(Y,LAMBDA(s,ROWS(TEXTSPLIT(s,,", ")))),3),CHOOSE({1\2},TOCOL(IF(SEQUENCE(,MAX(k))<=k,X,W),3),FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,SUBSTITUTE(Y,", ","</s><s>"))&"</s></t>","//s")))

this worked replacing the backslash with a comma. thanks mate..