How do I filter which will show only the unique options?

[QuestionBaker]

Consider the info in the table.
I want to apply a filter on Supplier which can identify different suppliers in the list
ABC, OPQ, XYZ, CAD
The drop down for the filter should show only the 4
I know I can go to text filters and select contains and then type the supplier, but a colleague asked for a solution from where only the unique supplier names are seen
Something like how tags work, but I do not know how to do it in excel
If the problem statement is confusing, let me know I will explain it once again

 

[maabadi]

Check this Page:

https://www.xelplus.com/searchable-drop-down-list/

 

[maabadi]

Use E2 formula for extracting Partial Match.
and create Dropdown based them at H1
Then Use one of H2 or H3 formula to See filtered result Based Data in H1

Book21.xlsm
	A	B	C	D	E	F	G	H
1	Part no.	Part name	Supplier		Extract		DropDown	OPQ
2	1	Ali1	ABC		ABC			FILTER($C$2:$C$17,ISNUMBER(SEARCH($H$1,$C$2:$C$17)),”Not Found”)
3	2	Ali2	OPQ		OPQ			UNIQUE(FILTER($C$2:$C$17,ISNUMBER(SEARCH($H$1,$C$2:$C$17)),”Not Found”))
4	3	Ali3	ABC, OPQ		XYZ
5	4	Ali4	OPQ, XYZ		CAD
6	5	Ali5	XYZ
7	6	Ali6	CAD
8	7	Ali7	ABC, OPQ, XYZ
9	8	Ali8	ABC, CAD
10	9	Ali9	CAD, XYZ
11	10	Ali10	OPQ, CAD
12	11	Ali11	ABC, CAD, OPQ
13	12	Ali12	ABC, CAD, XYZ
14	13	Ali13	ABC, XYZ
15	14	Ali14	CAD, OPQ
16	15	Ali15	CAD, OPQ, XYZ
17	16	Ali16	CAD, XYZ
Sheet2

Cell Formulas
Range	Formula
E2:E6	E2	=IFNA(INDEX(LEFT($C$2:$C$17,3),MATCH(0,COUNTIF($E$1:E1,LEFT($C$2:$C$17,3)),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Extract	=TblExtract[Extract]	E3:E6

Cells with Data Validation
Cell	Allow	Criteria
H1	List	=Extract

 

[Fluff]

Unfortunately there is no way do that with the Autofilter drop down.
But could possibly be done using advanced filter with some helper cells.