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

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.

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:

How to create an array formula

1. Select cell E3
2. Paste formula to cell
3. Press and hold Ctrl + Shift simultaneously.
4. Press Enter once.
5. 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

1. Select cell E3
2. Copy cell E3 (keyboard shortcut CTRL + c)
3. Select cell range E4:E29 with you mouse.
4. 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

1. Go to tab "Formulas"
2. Press with left mouse button on "Name Manager" button
3. Press with left mouse button on "New.."
4. Type UniqueStoreNumbers 
5. Select cell range =Data!$E$2:$E$27 in Refers to:
6. 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

1. Select cell A2
2. Go to tab "Data"
3. Press with left mouse button on "Data validation.." button
4. Go to tab "Settings"
5. Select List in Allow: field
6. Type in source: field: =UniqueStoreNumbers 
7. Press with left mouse button on OK

Copy drop down list and paste to cells below

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

Add vba code to module

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

How to enter the User defined function

Formula in cell B2:

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. […]

Excel categories

Comments (6)