Data validation list without duplicates

[Davidator]

Hi guys,
I've been searching everywhere but I can't seem to find the right solution to this problem.

Basically what I need is a drop-down list that ignores the duplicates, i.e:

Milk
Milk
Bread
Oil
Oil
Oil
Bread

The drop-down list would offer Milk, Bread and Oil as options (just an example. Real list is much biggger)
I've seen solutions that include creating an auxiliar list of the unique values, but this is not possible for me. Is there a way to achieve this with a single formula in the data validation source?

Much appreciated,
David

 

[vletm]

Why '... this is not possible ...' for You?

 

[bobhc]

You may find this video link of help

http://www.bluepecantraining.com/portfolio/cascading-drop-down-lists-in-excel/

And this has a lot of help/information

http://www.contextures.com/xlDataVal02.html


.

 

[Davidator]

You may find this video link of help

http://www.bluepecantraining.com/portfolio/cascading-drop-down-lists-in-excel/

And this has a lot of help/information

http://www.contextures.com/xlDataVal02.html


.

but this video is for cascading drop down lists, which is not what I am trying to do. What I need is just that from a (large) list with many duplicates, create a drop-down with the unique values, and that without the aid of n auxiliary table. I thought that wouldn't be such difficult problem, but I guess it's just not doable :/

 

[xlstime]

Hey Davidator,

First create unique list in separate column then create make dynamic drop down, please refer attachment

but this video is for cascading drop down lists, which is not what I am trying to do. What I need is just that from a (large) list with many duplicates, create a drop-down with the unique values, and that without the aid of n auxiliary table. I thought that wouldn't be such difficult problem, but I guess it's just not doable :/

 

[xlstime]

Please modify formula as per your requirement

Hey Davidator,

First create unique list in separate column then create make dynamic drop down, please refer attachment

 

[Nebu]

Hi:

Another method by using pivot.

Thanks