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:

ID

Category 1

Category 2

Category 3

Category 4

Category 5

Category 6

Category 7

Category 8

Category 9

Category 10

Category 11

Category 12

Category 13

Category 14

Category 15

Test1234

M

H

L

L

M

Testin511

M

M

M

L

M

L

<tbody>
</tbody>

Sheet2:

ID	Category Combined
Test1234	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
Testin511	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

<tbody>
</tbody>

 

[sandy666]

simple result with PowerQuery

ID	Custom
Test1234	Category 01 M Category 05 H Category 08 L Category 10 L Category 13 M
Testin511	Category 01 M Category 02 M Category 07 M Category 09 L Category 11 M Category 14 L

[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