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

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.

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

Please Login or Register  to view this content.

Then, Data Validation Source:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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