sort value to create drop down menu, without duplicate and blank cell

[Beatrice]

hi, I am working on a project, need to sort the value and create dependent drop down menu.

Here is the example:

For column " Sorted" formula as : =IFERROR(INDEX($AU$41:$AU$50,MATCH(AL41,$AV$41:$AV$50,0)),"")

How can I do it without manually go to the ribbon and sort every time?

Thanks in advance for your help.

 

[Peter_SSs]

Not certain what you are attempting. It it this?

21 06 17.xlsm
	A	B
1	Value	Sorted
2	240	80
3	240	180
4	240	240
5	240
6	80
7	80
8	80
9	80
10	80
11	180
12
Sort

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(AGGREGATE(15,6,A$2:A$11/(ISNA(MATCH(A$2:A$11,B$1:B1,0))),1),"")

 

[Beatrice]

Not certain what you are attempting. It it this?

21 06 17.xlsm
	A	B
1	Value	Sorted
2	240	80
3	240	180
4	240	240
5	240
6	80
7	80
8	80
9	80
10	80
11	180
12
Sort

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(AGGREGATE(15,6,A$2:A$11/(ISNA(MATCH(A$2:A$11,B$1:B1,0))),1),"")

Thanks for your help. I tried to adapt the given formula but the result look like this:

Did I misunderstand something?

CELL AX41 formula:
=IFERROR(AGGREGATE(15,6,$AU$41:$AU$50/(ISNA(MATCH($AU$41:$AU$50,AW$40:AW40,0))),1),"")

 

[Peter_SSs]

Did I misunderstand something?

Yes.

The column Highlighted below should be the column that the formula is in. That is, AX.
Note that my formula was in column B and it is column B that occupied that position in my formula.

CELL AX41 formula:
=IFERROR(AGGREGATE(15,6,$AU$41:$AU$50/(ISNA(MATCH($AU$41:$AU$50,AW$40:AW40,0))),1),"")

Also, I'm not sure if you are using columns AV & AW for something else, but they are not required for this formula to make its list.

 

[Beatrice]

Thanks Peter. I got it now.
I used a complicated way and not reaching what I wanted.
Your way is much simpler and works perfectly.
Thanks again for teaching me.
Have a good day.

 

[Beatrice]

@Peter_SSs
Sorry to bother you again... For that AX column, I used it to create a drop down menu, but it appears select always on the blank cells at the bottom.
How can I avoid it? I wished the list to be exact, without the blanks, which I also selected "ignore blank" from data validation but didn't work for me.

Thanks for your help.

 

[Peter_SSs]

One way would be like this, using a helper cell (AX39 in my case)

21 06 17.xlsm
	AU	AV	AW	AX	AY
39				$AX$41:$AX$43
40	Value
41	240			80
42	240			180
43	240			240
44	240
45	80
46	80
47	80
48	80
49	80
50	180
Sort

Cell Formulas
Range	Formula
AX39	AX39	=CELL("address",AX41)&":"&CELL("address",OFFSET(AX41,COUNT(AX41:AX50)-1,0,1,1))
AX41:AX50	AX41	=IFERROR(AGGREGATE(15,6,$AU$41:$AU$50/(ISNA(MATCH($AU$41:$AU$50,AX$40:AX40,0))),1),"")

Cells with Data Validation
Cell	Allow	Criteria
AY39	List	=INDIRECT($AX$39)