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

Beatrice

Board Regular
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
hi, I am working on a project, need to sort the value and create dependent drop down menu.

Here is the example:
Capture.PNG


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.
 

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
Joined
May 28, 2005
Messages
52,376
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
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?
Capture.PNG


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
Joined
May 28, 2005
Messages
52,376
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 17, 2019
Messages
85
Office Version
  1. 2019
Platform
  1. Windows
@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.
Capture.PNG

Thanks for your help.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,376
Office Version
  1. 365
Platform
  1. Windows
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
AX39AX39=CELL("address",AX41)&":"&CELL("address",OFFSET(AX41,COUNT(AX41:AX50)-1,0,1,1))
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)


1624001721245.png
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top