Use a drop down list to filter and concatenate unique distinct values

Author: Oscar Cronquist Article last updated on June 11, 2019

Question: Is there a way to have a unique list generated from a list? Meaning I have a sheet that contains my data and I want to generate a drop down list based on the input from one column. Which I already have.

Once the user selects the value from the drop down, I want it to generate another unique list just in a column.

For example the sheet contains store numbers (one column) and employee names (a second column), which may be listed multiple times. I want to create the drop down for the store number and then creating a list of employee names (non-repeating) that isn't a drop down, just a standard list is say A1, A2, A3, A4, etc

Is that clear as mud?

Thanks.

Answer:

The animated image above shows drop down lists in column A and a UDF in column B, once a value has been selected in the dropdown list the UDF looks up the value in a data set and returns the corresponding values on the same rows as the matching numbers concatenated to one cell with a comma as a delimiting character.

The UDF returns a blank in column B if the cell in column A is blank.

What you will learn in this article

Build an array formula that extracts unique distinct numbers sorted from low to high.
How to use a named range.
How to create a drop down list containing named range that points to numbers
How to create a user defined function that extracts and concatenates values based on a drop down list value.

Customize worksheet "Data"

Column A contains store numbers and column B contains the corresponding name. The formula in cell E3 extracts all numbers from column A ignoring duplicates, sorted from low to high or ascending order.

Array formula in cell E3:

=SMALL(IF(COUNTIF($E$1:E1, $A$2:$A$100)=0,$A$2:$A$100, ""), 1)

How to create an array formula

Select cell E3
Paste formula to cell
Press and hold Ctrl + Shift simultaneously.
Press Enter once.
Release all keys.

The formula is now surrounded with curly brackets, they appear automatically if you did the above steps correctly. Do not enter the curly brackets yourself.

How to copy array formula

Select cell E3
Copy cell E3 (keyboard shortcut CTRL + c)
Select cell range E4:E29 with you mouse.
Paste (Keyboard shortcut: CTRL+v)

Explaining formula in cell E3

Step 1 - Ignore previously returned numbers above current cell.

The COUNTIF function lets you check which numbers have been returned above the current cell, the first argument contains an expanding cell reference that grows when you copy the cell and paste to cells below.

COUNTIF($E$1:E1, $A$2:$A$100)

becomes

COUNTIF("Unqiue store numbers",{22; 24; 9; 12; 5; 17; 22; 23; 0; 1; 14; 20; 16; 3; 25; 1; 12; 12; 20; 8; 7; 1; 2; 2; 7; 14; 4; 14; 22; 21; 8; 15; 17; 9; 12; 7; 19; 12; 7; 18; 4; 1; 14; 17; 7; 10; 16; 4; 2; 12; 19; 15; 15; 8; 3; 14; 12; 1; 5; 19; 16; 8; 15; 7; 18; 18; 2; 3; 14; 19; 9; 15; 11; 17; 23; 6; 12; 5; 5; 25; 9; 14; 25; 22; 22; 24; 2; 22; 21; 1; 20; 11; 16; 5; 5; 21; 0; 12; 1})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}

If a value is 0 (zero) in the array then the corresponding number has not yet been returned in above cells.

Step 2 - Check if value in array equals 0 (zero)

The equal sign allows you to check if a value in the array is equal to a given condition.

COUNTIF($E$1:E1, $A$2:$A$100)=0

becomes

{0; 0; 0; ... ; 0}=0

and returns

{TRUE; TRUE; TRUE; ... ; TRUE}

The array is shortened.

Step 3 - Return value from column A if value in array is TRUE

The IF function lets you return a given value if the first argument evaluates to TRUE and another value if the argument evaluates to FALSE. We are working with arrays so the position of each value is important since the corresponding value is returned.

IF(COUNTIF($E$1:E1, $A$2:$A$100)=0, $A$2:$A$100, "")

becomes

IF({TRUE; TRUE; TRUE; ... ; TRUE}, {22; 24; 9; ... ; 1}, "")

and returns

{22; 24; 9; ... ; 1}.

Step 4 - Return the smallest value in array

The SMALL function allows you to extract the k-th smallest number, however, in this case we always extract the smallest number since the COUNTIF function keeps track of previously returned numbers.

SMALL(IF(COUNTIF($E$1:E1, $A$2:$A$100)=0,$A$2:$A$100, ""), 1)

becomes

SMALL({22; 24; 9; ... ; 1}, 1)

and returns 0.

Create a named range

Go to tab "Formulas"
Press with left mouse button on "Name Manager" button
Press with left mouse button on "New.."
Type UniqueStoreNumbers
Select cell range =Data!$E$2:$E$27 in Refers to:
Press with left mouse button on OK!

If you know you will be adding data later on then a dynamic named range is a better choice, it grows automatically when new data is entered.

Create a dynamic named range

Customize worksheet: Sheet1

Create drop down list

Select cell A2
Go to tab "Data"
Press with left mouse button on "Data validation.." button
Go to tab "Settings"
Select List in Allow: field
Type in source: field: =UniqueStoreNumbers
Press with left mouse button on OK

Copy drop down list and paste to cells below

Select cell A2.
Copy cell (CTRL + c).
Select cell range A3:A100.
Paste values (CTRL + v).

Add vba code to module

Copy VBA code below.
Press Alt+F11 to open the Visual Basic Editor.
Press with right mouse button on on workbook in Project Explorer
Press with mouse on Insert and then Module.
Paste VBA code to the code module.
Return to excel.

How to enter the User defined function

Formula in cell B2:

=Concat_Unique(A2;Data!$A$2:$A$100;Data!$B$2:$B$100)

Copy cell B2 and paste to cell range B3:B8.

Vba code

'Name User defined Function and input variables
Function Concat_Unique(Lookup_Value As String, Lookup_Column As Range, Concat_column As Range)

'Dimension variables and declare data types
Dim i As Single
Dim Unique As New Collection
Dim Value As Variant
Dim result As String

'Iterate through lookup column
For i = 1 To Lookup_Column.Cells.Rows.Count

    'Check if lookup value is equal to value in lookup column
    If Lookup_Value = Lookup_Column.Cells(i).Value Then

        'Check if character length is larger than 0 (zero)
        If Len(Concat_column.Cells(i)) > 0 Then

            'Enable error handling
            On Error Resume Next

            'Add value to collection. Returns an error if there is a duplicate in the collection
            Unique.Add Concat_column.Cells(i), CStr(Concat_column.Cells(i))

            'Disable error handling
            On Error GoTo 0
        End If
    End If
Next i

'Iterate through collection 
For Each Value In Unique

    'Add values in collection to a string variable with a comma as a delimiting character
    result = result & Value & ", "

'Continue with next value in collection
Next Value

'Check if variable result is empty
If Len(result) = 0 Then

    'Save a blank to variable result
    Concat_Unique = ""

'If not empty
Else

    'Remove two last characters (comma and a space character) and then return string to worksheet
    Concat_Unique = Left(result, Len(result) - 2)
End If

End Function

Get the Excel file

Willv3.xlsm

Concatenate category

Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Concatenate cell values
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Concatenate unique distinct values
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Drop down lists category

Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

More than 1300 Excel formulas

Excel categories

Conditonal Formatting

Pivot Tables

VBA

Macros

UDFs

6 Responses to “Use a drop down list to filter and concatenate unique distinct values”

Tom says:

May 24, 2012 at 4:56 pm

I am trying to create an estimating form that will calculate a total cost based of the values given. I have a drop down list, and i want it to produce a $ value for each item in a different cell. My goal is to create a form that will calculate total values.

Reply
Aynsley says:

September 13, 2012 at 4:50 pm

Hi,
I had a question. I have a excel costing sheet. What it is is a pull down menu with options to build a trailer.
So one pull down menu is tires. Then i pull down and it has a bunch of different tire options. My question is how do i get the price to change when i select different tires?
There are 3 tires. Goodyear is 500.00 RM is 400 and DL is 800.00
so i go to the pull down menu and choose a tire then i need the correct price to go in the price box beside that. I have aformula to do this but it's so long and i think it can be alot easier.

Right now this is the formula:
=IF(A51="X",VLOOKUP(B51,Sheet1!$A$2:$B$12001,2,FALSE),"-")

BUT what it does is look up on one sheet who gets its information from another sheet who gets its information from another sheet. So there are all these sheets and im pretty sure i don't need them all. It's just making this spreadsheet very large and very slow!

Please help if you can

Reply
- Oscar says:
  
  September 17, 2012 at 9:14 am
  
  Aynsley,
  
  Your formula seems to be fine.
- Aynsley says:
  
  September 17, 2012 at 2:09 pm
  
  yes, i know my formula is fine but i think it requires way too much work to do something that seems fairly simple.
  it takes it's information from the options work sheet. which looks the price up on the price worksheet which looks it up on another work sheet to get the inforamtion.
  it just seems like something could be cut somewhere.
srikanth says:

July 11, 2014 at 3:27 pm

first list AA, BB, CC
second list under AA - 11,22,33
under BB - 44, 55 etc

I have two column ... first column contains a list of AA,BB,CC

and in the next column it should return another list based on selection in the first column. for example if i select AA in the next column it should return another list with 11,22,33

is this posible???

Reply
Sourin Sett says:

December 29, 2016 at 11:58 am

I was trying to use drop down list with the help of data validation. But one problem is there, it does not take numbers or special characters. What could be the way out? as I do not have knowledge of vba will there be any other way out?

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Use a drop down list to filter and concatenate unique distinct values

What you will learn in this article

Customize worksheet "Data"

How to create an array formula

How to copy array formula

Explaining formula in cell E3

Step 1 - Ignore previously returned numbers above current cell.

Step 2 - Check if value in array equals 0 (zero)

Step 3 - Return value from column A if value in array is TRUE

Step 4 - Return the smallest value in array

Create a named range

Customize worksheet: Sheet1

Create drop down list

Copy drop down list and paste to cells below

Add vba code to module

How to enter the User defined function

Vba code

Get the Excel file

Concatenate category

Drop down lists category

Excel categories

6 Responses to “Use a drop down list to filter and concatenate unique distinct values”

Leave a Reply

How to comment