This post from ExcelFORO explains how to use VBA to create a sorted data validation list. It’s a great solution! Are there other ways to solve this?
(download my Excel file)
Sorted Drop Down List
The VBA code instantly refreshes when new entries are added. Perfect!
What about a formula solution?
Scroll to the comments section of his post. There’s a suggestion to use a dynamic array! These are being tested (365 insider edition) so I created a formula solution that works in all versions.
Pic 1 Kevin’s Formula Solution
My solution contains:
- city name input in field ‘ciudades’
- four helper formula fields (steps 1 to 4)
How does it work?
- Rank =COUNTIFS([ciudades],”<=”&[@ciudades]) ‘A Coruna’ = 1, ‘Zaragoza’ = 6(last)
- Counter =ROW([@ciudades])-ROW(Table13[[#Headers],[ciudades]]) basic counter
- Match =MATCH([@Counter],[Rank],0) find Counter value in Rank
- Sorted =INDEX([ciudades],[@Match]) index to get sorted city via Match
- Data Validation connects to a named range that references step 4 Sorted
Add a city below ‘Valencia’. The Data Validation list connects to a named range and updates instantly.
Pic 2 Data Validation
The helper columns are light and easy to audit. When you add a city the table, including all formulas, automatically expands!
Both the vba and formula solutions are worth considering.
UPDATE: Alternative Formula Solution
In the comments section below Robert Gascon suggested a solution that does not depend on an Excel table (my solution does).
I have now updated my Excel file (found at top of post) to include Robert’s solution! I’ve learned so much from Robert over the past few months!
What about a Power Query solution?
Power Query is an amazing tool that could load the ‘ciudad’ text, sort it and return it back to the sheet.
There’s one issue…if you add/modify city entries you need to refresh the query.
Using ‘Worksheet_Change’ the vba code updates automatically based on any addition or modification. The formulas are also automatic.
Is Power Query a bad solution? Not necessarily. If you had a large amount of data and didn’t want (a) heavy formulas or (b) vba code then Power Query could be the perfect solution! Power Query would load the data, do all the necessary steps, and then quietly drop the answers back into the sheet.
UPDATE: ExcelCampus has a nice blog post showing 4 different solutions. Jon’s formula solution is the way to solve it for those with the new 365 version of Excel. The solutions in my post by myself and Robert are compatible in any modern version of Excel (back to Excel 2007).
About ExcelFORO
excelforo.blogspot.com is one of the best Spanish Excel blogs that you’ll find! I can read Spanish fairly well so when I read Ismael’s posts my Excel and Spanish hobbies collide! Coool! 🙂
Check out Ismael’s Facebook page!
About Me
My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.
I saw a friend working on an Excel model back in 1996. I was hooked for life!
It took awhile to get into the workforce…but it’s been an amazing ride!
Hello Kevin,
Because I am not a fan of Excel Tables, just like Bill Jelen, I inserted Sheet 1 in your sample file. The labels in A1:B1 are ciudades and Alpha. I copied and listed the cities in Column A, starting in A2. Then, I defined “ciudades” with this formula:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
My formula in B2, copied down rows, is:
=LOOKUP(2,1/(COUNTIF(ciudades,”>=”&ciudades)=MAX(INDEX(
COUNTIF(ciudades,”>=”&ciudades)*(COUNTIF(B$1:B1,ciudades)=0),0))),
ciudades)
Thereafter, I defined “ciudadesAlpha” with this formula:
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))
Finally, I entered this formula in the source box of the Data Validation in D2:
=ciudadesAlpha
Bill Jelen’s link is here:
https://www.mrexcel.com/excel-tips/i-am-not-a-fan-of-excel-tables/
By the way, my lookup formula is similar to that which I posted as the last comment here:
https://www.myonlinetraininghub.com/excel-extract-a-unique-list
Cheers,
Robert
Thanks Robert for your solution! I have included it in my Excel file! I agree with you and BIll Jelen that there are many instances in which it’s better to not use a table. For me it all depends on the details as to whether I base my solution on a table or not. As always, thanks for reading and commenting! I enjoy seeing different solutions.
Cheers,
Kevin
Pingback: Excel Chess Games Viewer | My Spreadsheet Lab
Hello Kevin,
A shorter version of my earlier formula using AGGREGATE would be:
=LOOKUP(2,1/(COUNTIF(ciudades,”>=”&ciudades)=AGGREGATE(14,4,
COUNTIF(ciudades,”>=”&ciudades)*(COUNTIF(C$1:C1,ciudades)=0),1)),
ciudades)
Nonetheless, the shortest version I could come up with is through FREQUENCY, like this:
=LOOKUP(2,1/(FREQUENCY(ROWS($1:1),
COUNTIF(ciudades,”<="&ciudades)*(COUNTIF(D$1:D1,ciudades)=0))),
ciudades)
Cheers,
Robert
Thanks for the followup formula Robert. I will review it!
You might find my latest post interesting: https://myspreadsheetlab.com/humidex-formula-in-excel/
I’m sure you wouldn’t find 32 Celsius (+humidity) very hot.
Cheers,
Kevin
{=INDEX(Table1[ciudades],MATCH(ROWS($K$5:K5),MMULT(–(Table1[ciudades]>=TRANSPOSE(Table1[ciudades])),ROW(Table1[ciudades])^0),0))},
How about this by single step