Make Dropdown list without repeated value

Hi, refer to my attachment.
How to make a dropdown list from a list of table that have repeated value which the repeated value should not seen more than once.

Thank you for your time

Extending Bob's point, using your sample - insert a blank column between B & C (so Drop Down appears in Col D), then:

B1:
=IF(COUNTIF($A$1:$A1,$A1)>1,"",1+COUNTIF($A$1:$A$16,"<"&$A1))
copied to B16

C1:
=IF(ROWS(C$1:C1)>COUNT($B$1:$B$16),NA(),INDEX($A$1:$A$16,MATCH(SMALL($B$1:$B$16,ROWS(C$2:C2)),$B$1:$B$16,0)))
copied to C16

Then, Data Validation Source:

=OFFSET($C$1,0,0,COUNT($B$1:$B$16),1)

Hello,

you could use Advanced Filter to filter out unique values and then sort them.

Or you could use a helper column in column B and create a unique list in Column C.

see attached.

Got the point.
I must not sort the table. There DO formula work perfectly.
Thank you everyone

Why COUNTIF($A$1:$A$16,"<"&$A2) will have answer as the "<" will only work on number not character? see my attachment

Regards your question in the sample file...

The answer is 10 because there are 10 text strings in A1:A16 that precede "B2" in alphabet terms, namely:

A1 * 2
A2 * 2
A3 * 2
A4 * 2
B1 * 2

A precedes B and subsequently in terms of B1/B2 1 precedes 2

So that mean excel can calculate "<" in alphabet not only in number.
I never know that before. Sigh..

Yes.

When discussing COUNTIF & Data Types it's worth remembering that COUNTIF will always interpret a numeric criteria as a number even if comparing to text strings

To explain by means of example, create a new sheet:

=ROW()&""
copied down

Then assume that (bizarrely) you wanted to COUNT how many of your numeric strings were less "<5" in alphabet terms - ie 5 ("1","2","3","4","10") a COUNTIF would not work given:

=COUNTIF(A1:A10,"<"&"2")

will return 0 as the criteria is seen as <2 and there are no numbers in the range.
(you would use other functions for this - like SUMPRODUCT)

The above is not relevant here but is one of the little COUNTIF nuances to be aware of