Create a Line by Line Comma Separated String from Data Table

[Simonc64]

This is my sample datasheet.......

	A	B	C	D	E	F	G
1		100	200	300	400	500	Result
2	Customer A	Yes	Yes	No	Yes	No	100,200,400​
3	Customer B	Yes	Yes	No	Yes	Yes	100,200,400,500​
4	Customer C	No	Yes	Yes	No	No	200,300​

What I need is a formula to extract the "Yes" answers into a comma separated string as per column G; so where answer is "yes" in line 2, (or whichever line the answer is on) bring back the code in line 1 for each column A to F. I have tried a number of nested IF statements but cant seem to get it right - any help will be greatly appreciated

Thanks

Simon

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Simonc64]

It says Microsoft 365 Apps for Enterprise ?

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C	D	E	F	G	H
1		100	200	300	400	500	Result 365	Results (2016)
2	Customer A	Yes	Yes	No	Yes	No	100,200,400	100,200,400
3	Customer B	Yes	Yes	No	Yes	Yes	100,200,400,500	100,200,400,500
4	Customer C	No	Yes	Yes	No	No	200,300	200,300
Sheet23

Cell Formulas
Range	Formula
G2:G4	G2	=TEXTJOIN(",",TRUE,FILTER($B$1:$F$1,B2:F2="Yes"))
H2:H4	H2	=LEFT(CONCAT(IF(B2:F2="Yes",$B$1:$F$1&",","")),LEN(CONCAT(IF(B2:F2="Yes",$B$1:$F$1&",","")))-1)

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Column1"}, {{"Data", each _, type table [Column1=text, 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", {"Attribute", "Value", "Index"}, {"Custom.Attribute", "Custom.Value", "Custom.Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Custom.Value] = "Yes")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Custom.Value"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Attribute"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Column1", "1", "2", "3", "4", "5"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"1", "2", "3", "4", "5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Book2
	A	B	C	D	E	F
1	Column1	100	200	300	400	500
2	Customer A	Yes	Yes	No	Yes	No
3	Customer B	Yes	Yes	No	Yes	Yes
4	Customer C	No	Yes	Yes	No	No
5
6	Column1	Merged
7	Customer A	100,200,,400,
8	Customer B	100,200,,400,500
9	Customer C	,200,300,,
Sheet1

 

[Simonc64]

Thank you everyone for your answers - amazing help as always!