We need to extract unique values from a list to be used in a data validation drop down list. There are many ways to solve this. The best solution depends on asking the right questions.
Here are Leila’s original requirements. I will review several solutions (formula & non formula).
Visit Leila’s blog!
Ask Questions Before Creating Solution!
Video & Excel File
Light & Simple & Dynamic?
FAST/GOOD/CHEAP pick two! It’s often the same in Excel but we have LIGHT/SIMPLE/DYNAMIC.
Bill Jelen’s Solutions
Use built-in Excel tools! No formulas, no code! SUPER FAST but not dynamic. Watch Bill’s video
Oz du Soleil’s Solution
Power Query (Get & Transform) is amazing but it requires a refresh. Watch Oz’s video
Mike Girvin’s Solution
Leila Gharani’s Solution
Leila’s compact formula is amazing! Just be careful with expanding ranges if you have a lot of data.
If you need a dynamic and simple solution then consider my solution (see my video above).
In the Excel file (above) I made my solution more efficient (important for large data-sets).
Mike Rempel’s Solution
XLarium’s comment below show us how to use M code to automatically refresh a Get & Transform query!
Which Solution Is Your Favorite?
What do you think? Add a comment below.
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.
About 20 years ago I remember seeing a friend working on an Excel spreadsheet. It looked interesting. I thought to myself “What if I could learn more about Microsoft Excel than I know about Chess & Spanish?”
At the time it seemed like a distant possibility but I started learning and I’ve never looked back 🙂
I’m a Data Analyst at McKinsey & Company.