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.
Requirements
Here are Leila’s original requirements. I will review several solutions (formula & non formula).
Visit Leila’s blog!
Questions Before Creating Solution!
Video & Excel File
Or click here to watch. Download my 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
I love array formulas! This one is long but brilliant! Watch Mike’s videos here and here
Leila Gharani’s Solution
Leila’s compact formula is amazing! Just be careful with expanding ranges if you have a lot of data.
=IFERROR(INDEX(TableDiv[Division],MATCH(0,INDEX(COUNTIF($D$7:D7,TableDiv[Division]),),0)),””)
Leila’s video and blog. Here is the video in Polish by Piotr Majcher
My Solution
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
Mike reminds us to look for the super easy solution whenever possible. Great solution for data input. Mike’s video and post.
Updates
Piotr Majcher told me about this post from Oscar Cronquist. Great examples for creating unique / distinct list!
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.
About Me
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 🙂
Check out my YouTube videos and my blog posts.
I have problems writing my comment on YouTube. So it will be here.
I would enhance Oz’s solution adding automatic refresh after a change in the first table column.
Setup:
Sheet name: XLarium’s Solution
Table name: tbl_XLarium
M-Ccode:
let
Source = Excel.CurrentWorkbook(){[Name=”tbl_XLarium”]}[Content],
#”Removed Columns” = Table.RemoveColumns(Source,{“App”}),
#”Removed Duplicates” = Table.Distinct(#”Removed Columns”)
in
#”Removed Duplicates”
VBA-Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wb As Workbook
Dim ws As Worksheet
Dim cn As WorkbookConnection
Dim tbl As ListObject
Dim tbl_Column As Range
Set wb = ThisWorkbook
Set ws = wb.Worksheets(“XLarium’s Solution”)
Set tbl = ws.ListObjects(“tbl_XLarium”)
Set tbl_Column = tbl.ListColumns(1).DataBodyRange
If Not Intersect(Target, tbl_Column) Is Nothing Then
For Each cn In wb.Connections
If cn = “Abfrage – tbl_XLarium” Then
cn.Refresh
End If
Next cn
End If
End Sub
You may have to change “Abfrage – tbl_XLarium” in to “Query – tbl_XLarium”.
“automatic refresh after a change in the first table column…” that is awesome! Thanks XLarium.
With modern arrays you can add another solution with UNIQUE().
Maybe we get a follow-up on the video?