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 think the best answer to your question will depend on what Excel version you are using. Therefore I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also greatly assist potential helpers if they had that sample data and expected results in a form that they can easily copy to test with. Therefore, also have a look at XL2BB

 

[Craggs82]

shiny new versions of excel its as simple as

=FILTER(C:C,B:B=F3,"")

 

[Peter_SSs]

shiny new versions of excel its as simple as

=FILTER(C:C,B:B=F3,"")

I think that you may have overlooked the thread title (and repeated in the description & sample results) not to list duplicates. I also note that in the expected output the values are sorted.
I would also avoid whole column references in formulas like that.

My suggestion if the dynamic array functions are available is something like this where 100 in the formula just needs to be bigger than any likely data.

22 11 25.xlsm
	B	C	D	E	F	G	H
1
2
3	A	11			A	B	C
4	A	12			11	2	1
5	A	14			12	3	2
6	A	12			14	5	3
7	A	11				6	4
8	A	12				8
9	B	5
10	B	8
11	B	6
12	B	3
13	B	2
14	B	5
15	C	3
16	C	1
17	C	4
18	C	2
19	C	3
No dupes

Cell Formulas
Range	Formula
F4:F6,H4:H7,G4:G8	F4	=SORT(UNIQUE(FILTER($C3:$C100,$B3:$B100=F3,"")))
Dynamic array formulas.

 

[Peter_SSs]

.. and if the dynamic array functions are not available

22 11 25.xlsm
	B	C	D	E	F	G	H
1
2
3	A	11			A	B	C
4	A	12			11	2	1
5	A	14			12	3	2
6	A	12			14	5	3
7	A	11				6	4
8	A	12				8
9	B	5
10	B	8
11	B	6
12	B	3
13	B	2
14	B	5
15	C	3
16	C	1
17	C	4
18	C	2
19	C	3
No dupes (2)

Cell Formulas
Range	Formula
F4:H11	F4	=IFERROR(AGGREGATE(15,6,$C$3:$C$100/(($B$3:$B$100=F$3)*($C$3:$C$100>MAX(F$3:F3))),1),"")

 

[vince1985]

many thanks for your feedbacks. I shall try these later today and will then get back to you !
I am using Excel 2016.

 

[Peter_SSs]

I am using Excel 2016.

Please put that in your account details as suggested in post 32 so that the information is always available to helpers

 

[vince1985]

as the dynamic array functions are not available (I am going to look at this later), I used the second formula that worked perfectly for me !
Thank you for your help !
(and i have updated my profile)

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and thanks for updating your profile.

 

[vince1985]

Hello everyone,

Thank you again for the formulae above that helped me greatly already.
Due to the formula structure, it works only if the data to be displayed once are numbers. I wanted to know also, how the formula would look like if the data from which we needed to remove duplicates were not numbers (example hereunder) ?
(data do not need to be sorted into a specific order, most important being to remove duplicates)

thank you in advance for your help !

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G
10	Criteria	value		Expected output
11	A	A		criteria	A	B	C
12	A	B		values	A	A	Q
13	A	C			B	V	A
14	A	A			V	C	B
15	A	A			D	B	C
16	A	D				S
17	B	A			https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/
18	B	V			https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/
19	B	C			https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/	https://www.mrexcel.com/board/threads/formula-to-remove-doubles-in-excel.1223078/
20	B	B
21	B	S
22	B	S
23	C	Q
24	C	A
25	C	B
26	C	B
27	C	C
Sheet1