Formula to remove doubles in excel

[vince1985]

good morning,

I would like to submit an issue i am often facing on Excel.
I am uploading an example hereunder in order to illustrate my request.

I have a 2 entries table and i would like to depict, according to a specific criteria, each of its value only once, without using a pivot table nor vba

In the enclosed example there is a table where one line = one student. Students are in 3 classrooms. I would like to have a formulae showing, for each classroom (which is my criteria then), the different ages of its students (hence only depicting only once the ages that are in double in this classroom).
I have a formula that does the job without criteria. However, I do not know any formula working with a criteria.

Has anyone already faced this case before ?

thank you in advance for your support !

 

[Peter_SSs]

I don't understand your expected results. How do you get a value of V for criteria A? Hopefully an error.
This is also a bit trickier if the criteria and the values can be the same. For now, I have assumed that they would be different. Post back if we have to cope with them being the same.

vince1985.xlsm
	A	B	C	D	E	F	G
1	Criteria	value		Output
2	A	AA		criteria	A	B	C
3	A	BB		values	AA	AA	QQ
4	A	CC			BB	VV	AA
5	A	AA			CC	CC	BB
6	A	AA			DD	BB	CC
7	A	DD				SS
8	B	AA
9	B	VV
10	B	CC
11	B	BB
12	B	SS
13	B	SS
14	C	QQ
15	C	AA
16	C	BB
17	C	BB
18	C	CC
No dupes (3)

Cell Formulas
Range	Formula
E3:G18	E3	=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$18)/(($A$2:$A$18=E$2)*(ISNA(MATCH($B$2:$B$18,E$2:E2,0)))),1)),"")

 

[vince1985]

Many thanks for your feedback.
Yes, the value V in the expected results was an error on my side and should not have been displayed. The criteria and values would also always be different.
=> Hence, I think that the formula that you posted shall work for me. I will check that later today and get back to you.
Thank you again

 

[vince1985]

I do confirm this formula worked perfectly for me. It works also very well with numeric data (only difference being that these are not sorted).

It is also easy to run the same formula without criteria or with several criteria by removing/adding condition(s) : ($N$4:$N$20=R$4)

A huge thank you for your help Peter !

 

[Peter_SSs]

Cheers. Glad to help.