IF statement with Concatenate

[shre0047]

I'm trying to do a concatenate formula to combine all the categories from sheet 1 into one cell. The values in Sheet1 for categories would be a drop down list of blank, L, M, and H. In Sheet 2, if the category has a value, include it in part of the concatenate formula. Is an loaded IF statement the most efficient way or would a macro make more logical sense?

Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD]Category 5[/TD]
[TD]Category 6[/TD]
[TD]Category 7[/TD]
[TD]Category 8[/TD]
[TD]Category 9[/TD]
[TD]Category 10[/TD]
[TD]Category 11[/TD]
[TD]Category 12[/TD]
[TD]Category 13[/TD]
[TD]Category 14[/TD]
[TD]Category 15[/TD]
[/TR]
[TR]
[TD]Test1234[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Testin511[/TD]
[TD]M[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD]L[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Category Combined[/TD]
[/TR]
[TR]
[TD]Test1234[/TD]
[TD]Area 1: Category 1
Category Value: Medium

Area 2: Category 5
Category Value: High

Area 3: Category 8
Category Value: Low

Area 4: Category 10
Category Value: Low

Area 5: Category 13
Category Value: Medium[/TD]
[/TR]
[TR]
[TD]Testin511[/TD]
[TD]Area 1: Category 1
Category Value: Medium

Area 2: Category 2
Category Value: Medium

Area 3: Category 7
Category Value: Medium

Area 4: Category 9
Category Value: Low

Area 5: Category 11
Category Value: Medium

Area 6: Category 14
Category Value: Low[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

simple result with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Test1234[/td][td=bgcolor:#E2EFDA]Category 01
M
Category 05
H
Category 08
L
Category 10
L
Category 13
M[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Testin511[/td][td]Category 01
M
Category 02
M
Category 07
M
Category 09
L
Category 11
M
Category 14
L[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ID"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Attribute", "Value"},Combiner.CombineTextByDelimiter("#", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Merged Columns", {{"Merged", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"ID"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Count],"Merged")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Columns",{{"ID", Order.Ascending}, {"Custom", Order.Ascending}})
in
    #"Sorted Rows1"[/SIZE]

the rest is up to you, with Area # and Category Value