Extract multiple matches which are unique into separate columns

[cr9kus]

I have a following table.

Name	Value
X	10
Y	20
Z	30
X	40
Y	50
Z	60
X	10
Y	60

I wish to extract all the unique values corresponding to the name in separate columns. The output shall come out like this. Since X has 10 as a value twice, I want it to be considered only once.

Name	Value 1	Value 2	Value 3
X	10	40
Y	20	50	60
Z	30	60

Array Formula Used
=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=$E2,ROW($B:$B)-MIN(ROW($B:$B))+1),COLUMNS($E$2:E2))),"")

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G
1	Name	Value		Name	Value 1	Value 2	Value 3
2	X	10		X	10	40
3	Y	20		Y	20	50	60
4	Z	30		Z	30	60
5	X	40
6	Y	50
7	Z	60
8	X	10
9	Y	60
10
Sheet26

Cell Formulas
Range	Formula
D2:D4	D2	=LET(u,SORT(UNIQUE(A2:A20)),FILTER(u,u<>""))
E2:F2,E4:F4,E3:G3	E2	=TRANSPOSE(UNIQUE(FILTER($B$2:$B$20,$A$2:$A$20=D2)))
Dynamic array formulas.

 

[cr9kus]

thank you for your solution. Can this be done without using the newer formula as it might not work if used in older versions. Is there any way you can advise which can be used in office 19.

 

[cr9kus]

Welcome to the MrExcel forum!

Try:

Book3
	A	B	C	D	E	F	G
1	Name	Value		Name	Value 1	Value 2	Value 3
2	X	10		X	10	40
3	Y	20		Y	20	50	60
4	Z	30		Z	30	60
5	X	40
6	Y	50
7	Z	60
8	X	10
9	Y	60
10
Sheet26

Cell Formulas
Range	Formula
D2:D4	D2	=LET(u,SORT(UNIQUE(A2:A20)),FILTER(u,u<>""))
E2:F2,E4:F4,E3:G3	E2	=TRANSPOSE(UNIQUE(FILTER($B$2:$B$20,$A$2:$A$20=D2)))
Dynamic array formulas.

thank you for your solution. Can this be done without using the newer formula as it might not work if used in older versions. Is there any way you can advise which can be used in office 19.

 

[Eric W]

If you have a special request that the solution be usable in earlier versions of Excel, please let us know in the original post, so we don't have to solve the problem twice. Try:

Book3
	A	B	C	D	E	F	G	H
1	Name	Value		Name	Value 1	Value 2	Value 3
2	X	10		X	10	40
3	Y	20		Y	20	50	60
4	Z	30		Z	30	60
5	X	40
6	Y	50
7	Z	60
8	X	10
9	Y	60
10
Sheet26

Cell Formulas
Range	Formula
E2:H4	E2	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$20)/($A$2:$A$20=$D2)/(COUNTIF($D2:D2,$B$2:$B$20)=0),1)),"")
D2:D7	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/($A$2:$A$20<>"")/(COUNTIF($D$1:$D1,$A$2:$A$20)=0),1)),"")

This should work in Excel 2010 and newer.

 

[cr9kus]

If you have a special request that the solution be usable in earlier versions of Excel, please let us know in the original post, so we don't have to solve the problem twice. Try:

Book3
	A	B	C	D	E	F	G	H
1	Name	Value		Name	Value 1	Value 2	Value 3
2	X	10		X	10	40
3	Y	20		Y	20	50	60
4	Z	30		Z	30	60
5	X	40
6	Y	50
7	Z	60
8	X	10
9	Y	60
10
Sheet26

Cell Formulas
Range	Formula
E2:H4	E2	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$20)/($A$2:$A$20=$D2)/(COUNTIF($D2:D2,$B$2:$B$20)=0),1)),"")
D2:D7	D2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/($A$2:$A$20<>"")/(COUNTIF($D$1:$D1,$A$2:$A$20)=0),1)),"")

This should work in Excel 2010 and new

Sorry, I should have specified in the first go. Thank you for your solution.