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 isnt a drop down, just a standard list is say A1,A2,A3,A4,etc

Is that clear as mud?

Thanks.

Answer:

This workbook contains:

  • Array formula (Sheet: Data) sorts and filters unique distinct store numbers
  • Named range: UniqueStoreNumbers =Data!$E$2:$E$27
  • Drop down list (data validation) (Sheet: Sheet1)
  • Custom function: Concat_Unique (Sheet: Sheet1)
  • Sheets: Sheet1 and Data

Sheet: Data

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

  1. Select cell E3
  2. Paste formula
  3. Press and hold Ctrl + Shift
  4. Press Enter

How to copy array formula

  1. Select cell E3
  2. Copy cell (not formula)
  3. Select cell range E4:E29
  4. Paste

Create a named range

  1. Go to tab "Formulas"
  2. Click "Name Manager" button
  3. Click "New.."
  4. Type UniqueStoreNumbers 
  5. Select cell range =Data!$E$2:$E$27 in Refers to:
  6. Click OK!

Sheet: Sheet1

Create drop down list

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

Add vba code to module

  1.  Copy vba code below
  2. Press Alt+F11
  3. Right click on workbook in project explorer
  4. Select Insert and Module
  5. Paste vba code to code module
  6. Return to excel

Vba code

Function Concat_Unique(Lookup_Value As String, Lookup_Column As Range, Concat_column As Range)
Dim i As Single
Dim Unique As New Collection
Dim Value As Variant
Dim result As String
For i = 1 To Lookup_Column.Cells.Rows.Count
    If Lookup_Value = Lookup_Column.Cells(i).Value Then
        If Len(Concat_column.Cells(i)) > 0 Then
            On Error Resume Next
            Unique.Add Concat_column.Cells(i), CStr(Concat_column.Cells(i))
            On Error GoTo 0
        End If
    End If
Next i
For Each Value In Unique
    result = result & Value & ", "
Next Value
If Len(result) = 0 Then
    Concat_Unique = ""
Else
    Concat_Unique = Left(result, Len(result) - 2)
End If
End 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.

Download excel *.xlsm file

Will.xlsm

Related posts:

Use filtered table values in a drop down list (vba)

Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula

Extract dates using a drop down list in excel

Create dependent drop down lists containing unique distinct values in multiple rows

Create dependent drop down lists containing unique distinct values in excel