# Video 00172 Unique Values for Data Validation Drop Down

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!

## 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.

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.

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.

## 3 Comments →Video 00172 Unique Values for Data Validation Drop Down

1. XLarium

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”.

1. Kevin Lehrbass

“automatic refresh after a change in the first table column…” that is awesome! Thanks XLarium.

2. XLarium

With modern arrays you can add another solution with UNIQUE().
Maybe we get a follow-up on the video?