Drop down list using unique but with changed number of rows

[zinah]

Hi,

I was able to create a a data validation list by using UNIQUE, however, my rows may change whenever I update the template. Can anyone advise how can I change my current formula to consider the rows changes?

FilterDropDowns.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Type	Style	Size	Color	Price				Gray	Orange
3	T-Shirt	Basic	S	Gray	14				Blue
4	T-Shirt	Basic	S	Blue	18				White
5	T-Shirt	Basic	M	White	17				Black
6	T-Shirt	Basic	M	Gray	20				Red
7	T-Shirt	Basic	M	Black	17				Yellow
8	T-Shirt	Ringer	S	Red	19				Pink
9	T-Shirt	Ringer	S	Yellow	13				Orange
10	T-Shirt	Ringer	M	Pink	16
11	T-Shirt	Ringer	M	Red	13
12	T-Shirt	Ringer	M	Orange	10
13	T-Shirt	Ringer	L	Gray	20
14	T-Shirt	Ringer	L	Blue	16
15	T-Shirt	Fitted	S	White	10
16	T-Shirt	Fitted	S	Gray	15
17	T-Shirt	Fitted	L	Black	12
18	T-Shirt	Fitted	L	Red	15
19	T-Shirt	Fitted	L	Yellow	15
20	T-Shirt	V-Neck	S	Pink	20
21	T-Shirt	V-Neck	S	Red	17
22	T-Shirt	V-Neck	S	Orange	11
23	T-Shirt	V-Neck	M	Gray	20
24	T-Shirt	V-Neck	M	Blue	12
25	T-Shirt	V-Neck	L	White	18
26	T-Shirt	V-Neck	L	Gray	14
27	Hoodie	Basic	M	Black	12
28	Hoodie	Basic	M	Red	10
29	Hoodie	Basic	L	Yellow	11
30	Hoodie	Basic	L	Pink	10
31	Hoodie	Basic	L	Red	12
32	Hoodie	Basic	XL	Orange	11
33	Hoodie	Basic	XL	Gray	17
34	Hoodie	Zip Up	M	Blue	15
35	Hoodie	Zip Up	M	White	20
36	Hoodie	Zip Up	M	Gray	10
37	Hoodie	Zip Up	L	Black	12
38	Hoodie	Zip Up	L	Red	11
39	Hoodie	Zip Up	XL	Yellow	15
40	Hoodie	Zip Up	XL	Pink	10
41	Hoodie	Zip Up	XL	Red	16
42	Sweatshirt	Basic	S	Orange	20
43	Sweatshirt	Basic	S	Gray	14
44	Sweatshirt	Basic	L	Blue	19
45	Sweatshirt	Basic	L	White	16
46	Sweatshirt	Basic	L	Gray	19
47	Sweatshirt	Basic	XL	Black	15
48	Sweatshirt	Basic	XL	Red	13
49
Sheet1

Cell Formulas
Range	Formula
I2:I9	I2	=UNIQUE(D3:D48)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=$I$2#

 

[Fluff]

How about

=UNIQUE(FILTER(D3:D1000,D3:D1000<>""))

 

[zinah]

How about

=UNIQUE(FILTER(D3:D1000,D3:D1000<>""))

That works perfectly great, however, in my real data, I need to include one blank option in my drop down list, how can I do that?

FilterDropDowns.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Type	Style	Size	Color	Price				Gray	Orange
3	T-Shirt	Basic	S	Gray	14				Blue
4	T-Shirt	Basic	S	Blue	18				White
5	T-Shirt	Basic	M	White	17				Black
6	T-Shirt	Basic	M	Gray	20				Red
7	T-Shirt	Basic	M	Black	17				Yellow
8	T-Shirt	Ringer	S	Red	19				Pink
9	T-Shirt	Ringer	S	Yellow	13				Orange
10	T-Shirt	Ringer	M	Pink	16
11	T-Shirt	Ringer	M	Red	13
12	T-Shirt	Ringer	M	Orange	10
13	T-Shirt	Ringer	L	Gray	20
14	T-Shirt	Ringer	L	Blue	16
15	T-Shirt	Fitted	S	White	10
16	T-Shirt	Fitted	S	Gray	15
17	T-Shirt	Fitted	L	Black	12
18	T-Shirt	Fitted	L	Red	15
19	T-Shirt	Fitted	L	Yellow	15
20	T-Shirt	V-Neck	S	Pink	20
21	T-Shirt	V-Neck	S	Red	17
22	T-Shirt	V-Neck	S	Orange	11
23	T-Shirt	V-Neck	M	Gray	20
24	T-Shirt	V-Neck	M	Blue	12
25	T-Shirt	V-Neck	L	White	18
26	T-Shirt	V-Neck	L	Gray	14
27	Hoodie	Basic	M	Black	12
28	Hoodie	Basic	M	Red	10
29	Hoodie	Basic	L	Yellow	11
30	Hoodie	Basic	L	Pink	10
31	Hoodie	Basic	L	Red	12
32	Hoodie	Basic	XL	Orange	11
33	Hoodie	Basic	XL	Gray	17
34	Hoodie	Zip Up	M	Blue	15
35	Hoodie	Zip Up	M	White	20
36	Hoodie	Zip Up	M	Gray	10
37	Hoodie	Zip Up	L	Black	12
38	Hoodie	Zip Up	L	Red	11
39	Hoodie	Zip Up	XL	Yellow	15
40	Hoodie	Zip Up	XL	Pink	10
41	Hoodie	Zip Up	XL	Red	16
42	Sweatshirt	Basic	S	Orange	20
43	Sweatshirt	Basic	S	Gray	14
44	Sweatshirt	Basic	L	Blue	19
45	Sweatshirt	Basic	L	White	16
46	Sweatshirt	Basic	L	Gray	19
47	Sweatshirt	Basic	XL	Black	15
48	Sweatshirt	Basic	XL	Red	13
49	Sweatshirt	Basic	XL		13
Sheet1

Cell Formulas
Range	Formula
I2:I10	I2	=IF(UNIQUE(D3:D49)=0,"",UNIQUE(D3:D49))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
J2	List	=$I$2#

 

[Fluff]

How about

=vstack("",UNIQUE(FILTER(D3:D1000,D3:D1000<>"")))

 

[zinah]

How about

=vstack("",UNIQUE(FILTER(D3:D1000,D3:D1000<>"")))

Thanks a LOT! This works perfectly great and this is the first time I know about the

vstack

formula, thank you for your usual support!

 

[Fluff]

You're welcome & thanks for the feedback.