Consolidate Duplicate Cells in Column A and output products associated in Column B Horizontally

[dkmusf]

Hi There,

Looking for a formula that will consolidate column A with the products in Column B into a horizontal line that I can drag down in column C that will output in the Desired Output.

Initial Data

Data-11	Product1
Data-11	Product2
Data-12	Product3
Data-12	Product3
Data-12	Product4

Desired Output for Column C

Data-11	Product1	Product2
Data-12	Product3	Product4

Thanks,
Donny

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B	C	D	E	F	G	H	I
1	Data-11	Product1					Data-11	Product1	Product2
2	Data-11	Product2					Data-12	Product3	Product4
3	Data-12	Product3
4	Data-12	Product3
5	Data-12	Product4
Sheet1

Cell Formulas
Range	Formula
G1:G2	G1	=UNIQUE(A1:A5)
H1:I2	H1	=TRANSPOSE(UNIQUE(FILTER($B$1:$B$5,$A$1:$A$5=G1)))
Dynamic array formulas.

 

[Fluff]

Another option is you have the new functions like VSTACK.

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	Data-11	Product1	Data-11	Product1	Product2
3	Data-11	Product2	Data-12	Product3	Product3	Product4
4	Data-12	Product3
5	Data-12	Product3
6	Data-12	Product4
7
Data

Cell Formulas
Range	Formula
C2:F3	C2	=IFNA(DROP(REDUCE("",UNIQUE(FILTER(A2:A100,A2:A100<>"")),LAMBDA(a,b,VSTACK(a,HSTACK(b,TRANSPOSE(FILTER(B2:B100,A2:A100=b)))))),1),"")
Dynamic array formulas.

 

[dkmusf]

Thank you JamesCanale and Fluff for such a quick response and different ways to output the results. It has been years since I have been back to MrExcel...late 90s. You were the first to pop into my mind to figure out this excel formula.

Cheers!!

 

[Fluff]

Glad we could help & thanks for the feedback.