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

#### Beatrice

##### Board Regular
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?

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Peter_SSs

##### MrExcel MVP, Moderator
Not certain what you are attempting. It it this?

21 06 17.xlsm
AB
1ValueSorted
224080
3240180
4240240
5240
680
780
880
980
1080
11180
12
Sort
Cell Formulas
RangeFormula
B2:B8B2=IFERROR(AGGREGATE(15,6,A\$2:A\$11/(ISNA(MATCH(A\$2:A\$11,B\$1:B1,0))),1),"")

#### Beatrice

##### Board Regular
Not certain what you are attempting. It it this?

21 06 17.xlsm
AB
1ValueSorted
224080
3240180
4240240
5240
680
780
880
980
1080
11180
12
Sort
Cell Formulas
RangeFormula
B2:B8B2=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

##### MrExcel MVP, Moderator
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

##### Board Regular

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

##### Board Regular
@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.

#### Peter_SSs

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

21 06 17.xlsm
AUAVAWAXAY
39\$AX\$41:\$AX\$43
40Value
4124080
42240180
43240240
44240
4580
4680
4780
4880
4980
50180
Sort
Cell Formulas
RangeFormula
AX41:AX50AX41=IFERROR(AGGREGATE(15,6,\$AU\$41:\$AU\$50/(ISNA(MATCH(\$AU\$41:\$AU\$50,AX\$40:AX40,0))),1),"")
Cells with Data Validation
CellAllowCriteria
AY39List=INDIRECT(\$AX\$39)

Replies
0
Views
548
Replies
2
Views
248
Replies
2
Views
1K
Replies
0
Views
225
Replies
1
Views
404

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,579
Messages
5,770,969
Members
425,653
Latest member
UNSING

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back