Identify subelements related to a sku in a separate table
Please check whether the formula is helpful.
=TEXTJOIN(";",TRUE,IF(LEFT($A$2:A$8,FIND("_",$A$2:$A$8)-1)=A12,$A$2:$A$8,""))
Here is a solution with pivot table (works in Office 365)
- add a helper column with your main category, formula:
=LEFT(A2,FIND("_",A2)-1) - insert a pivot table, make sure you check "add this data to the data model"
- move your category to rows
- in power pivot tab select measures - new measure
- enter this formula then press OK
=CONCATENATEX(Range,[Variant],",")
- add your measure in values part of pivot table
