Comma separated to columnar

[tigerzen]

Thought this would be easier but need to present data in columns as per this example. Really looking for the simplest way possible to do this so if there are any commands like Find and Replace that would do it, great, otherwise formulae/function and if that is not possible then VBA.

Crtieria map updated.xlsb.xlsx
	A	B	C	D	E	F	G
1					Required output
2	HR	Sales	Admin		HR	Sales	Admin
3	Tom, Fred	Sally, Ken, Peter	Victor, Steve, Noel, Adam		Tom	Sally	Victor
4					Fred	Ken	Steve
5						Peter	Noel
6							Adam
Sheet3

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Unpivoted Columns", {{"Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Value", Text.Trim, type text}}),
    #"Grouped Rows" = Table.Group(#"Trimmed Text", {"Attribute"}, {{"Data", each _, type table [Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Value", "Index"}, {"Custom.Value", "Custom.Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Attribute]), "Attribute", "Custom.Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom.Index"})
in
    #"Removed Columns1"

Book4
	A	B	C	D	E	F	G
3	HR	Sales	Admin		HR	Sales	Admin
4	Tom, Fred	Sally, Ken, Peter	Victor, Steve, Noel, Adam		Tom	Sally	Victor
5					Fred	Ken	Steve
6						Peter	Noel
7							Adam
Sheet1

 

[Peter_SSs]

Really looking for the simplest way possible

Like this?

23 06 07.xlsm
	A	B	C	D	E	F	G
1	HR	Sales	Admin		HR	Sales	Admin
2	Tom, Fred	Sally, Ken, Peter	Victor, Steve, Noel, Adam		Tom	Sally	Victor
3					Fred	Ken	Steve
4						Peter	Noel
5							Adam
6
Columns

Cell Formulas
Range	Formula
E2:E3,G2:G5,F2:F4	E2	=TEXTSPLIT(A2,,", ")
Dynamic array formulas.

 

[Peter_SSs]

Like this?

BTW, if you happen to not have the TEXTSPLIT function yet, an alternative formula is not too much longer.

23 06 07.xlsm
	A	B	C	D	E	F	G
1	HR	Sales	Admin		HR	Sales	Admin
2	Tom, Fred	Sally, Ken, Peter	Victor, Steve, Noel, Adam		Tom	Sally	Victor
3					Fred	Ken	Steve
4						Peter	Noel
5							Adam
Columns (2)

Cell Formulas
Range	Formula
E2:E3,G2:G5,F2:F4	E2	=FILTERXML("<p><c>"&SUBSTITUTE(A2,", ","</c><c>")&"</c></p>","//c")
Dynamic array formulas.

 

[tigerzen]

Like this?

23 06 07.xlsm
	A	B	C	D	E	F	G
1	HR	Sales	Admin		HR	Sales	Admin
2	Tom, Fred	Sally, Ken, Peter	Victor, Steve, Noel, Adam		Tom	Sally	Victor
3					Fred	Ken	Steve
4						Peter	Noel
5							Adam
6
Columns

Cell Formulas
Range	Formula
E2:E3,G2:G5,F2:F4	E2	=TEXTSPLIT(A2,,", ")
Dynamic array formulas.

That is beautiful, can't ask for more than that.

 

[Peter_SSs]

Cheers. Glad it worked for you. I agree that it can't get much simpler.