Data Validation - List with unique values

[Robandemmy]

Hello,

A few times a week i dump an extract into a speadsheet that i have to monitor project costs. The speadsheet has multiple helper columns i use to make looking things up much easier. Is there a way to create drop down list of column "A" that will only return the entries once? I would like to have the following:

Red
Red
Red
Blue
Green
Blue
Red
Red
Blue

I would like to have a dropdown list that shows only Red, Blue and Green. My list is much larger (~95000 lines with ~800 unique values)

 

[Zack Barresse]

Hi there,

Take a look at this video. It shows a great method for doing this, which doesn't require any VBA or formulas. Very slick.

 

[DRSteele]

If you have up-to-date software (Excel 365) you can put a Data Validation rule in cell E3 that refers to the extracted and sorted unique list of the items in the range in ColA.

MrExcel posts18.xlsx
	A	B	C	D	E
3	Red		Blue		Green
4	Red		Green
5	Red		Red
6	Blue
7	Green
8	Blue
9	Red
10	Red
11	Blue
Sheet13

Cell Formulas
Range	Formula
C3:C5	C3	=SORT(UNIQUE(A3:A11))
Dynamic array formulas.