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!

 

 

Ask Questions Before Creating Solution!

 

 

Video & Excel File

Or click here to watch. Download my Excel file here.

 

 

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.

3 Comments

  1. XLarium says:

    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 says:

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

  2. XLarium says:

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

Post a comment