Consolidating text (Concatenate?)

[northw]

Hello,
I've just not been able to find a solution to this.. I need to create a seperate table merging text based on key (SKU). Please see this example, the table on the left is source. And the one on the right handside is merged text.

Basically it merges text based on SKU (key), and ignores duplicates. Text is then separated by pipe.

How can I achieve this? (Office 365 on Mac, but also has access to Excel on Windows via VM).

Many thanks!

Product Code	Colours	Sizes		SKU	Colours	Sizes
SKU1	Red	S		SKU1	Red|Yellow	S|M|L|XL
SKU1	Red	M		SKU2	Blue|Red	XS]S|M|L|XL|XXL
SKU1	Red	L
SKU1	Yellow	S
SKU1	Yellow	M
SKU1	Yellow	L
SKU1	Yellow	XL
SKU2	Blue	XS
SKU2	Blue	S
SKU2	Blue	M
SKU2	Blue	L
SKU2	Red	M
SKU2	Red	L
SKU2	Red	XL
SKU2	Red	XXL

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Product Code	Colours	Sizes		SKU	Colours	Sizes
2	SKU1	Red	S		SKU1	Red|Yellow	S|M|L|XL
3	SKU1	Red	M		SKU2	Blue|Red	XS|S|M|L|XL|XXL
4	SKU1	Red	L
5	SKU1	Yellow	S
6	SKU1	Yellow	M
7	SKU1	Yellow	L
8	SKU1	Yellow	XL
9	SKU2	Blue	XS
10	SKU2	Blue	S
11	SKU2	Blue	M
12	SKU2	Blue	L
13	SKU2	Red	M
14	SKU2	Red	L
15	SKU2	Red	XL
16	SKU2	Red	XXL
17
Sheet5

Cell Formulas
Range	Formula
E2:E3	E2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
F2:G3	F2	=TEXTJOIN("|",,UNIQUE(FILTER(B$2:B$100,$A$2:$A$100=$E2)))
Dynamic array formulas.

 

[northw]

Incredible, thank you! Easy when you know - I've spent days trying to figure it out.

Many thanks!

 

[Fluff]

You're welcome & thanks for the feedback.