Dynamic Drop Down List in Excel

Question

My data is arranged in this way:

rowA     B                   
1   ABC Description 1
2   XYZ Description 2
3   MNODescription 3
4   ABC Description 4
5   MNODescription 5

Now in another worksheet in cell A1 there should be a drop down list based on Column A above. On Clicking it ABC, XYZ & MNO shall be shown in drop down. Then when I select, say, ABC in A1; in B1 both the descriptions (Description 1 & Description 4) shall be shown in a drop down list.

Answer 1

I wrote this function which concatenates all matches:

Public Function ConcatMatches(ByRef rgFind As Range, ByRef rgSource As Range, ByVal lngOffset As Long) As String
  Dim rgHit As Range, firstAddress As String, noWrap As Boolean
  Set rgHit = rgSource.Find(rgFind.Value)

  'ensure no wrapping occurs to avoid infinite loops
  firstAddress = rgHit.Address
  noWrap = True

  Dim concat As String
  While Not (rgHit Is Nothing) And noWrap
    If concat <> "" Then
        concat = concat & ", "
    End If
    concat = concat & rgHit.Offset(0, lngOffset)

    'find next and ensure we didn't wrap back to first hit
    Set rgHit = rgSource.Find(rgFind.Value, rgHit)
    noWrap = (firstAddress <> rgHit.Address)
  Wend

  ConcatMatches = concat
End Function

And this function to display only the unique values from a range (for the data validation), enter it as an Array Formula + use a dynamic named range. I show how to use them below:

Public Function GetUniques(rgList As Range) As Variant
    'prepare return array matching calling range dimensions
    Dim CallerRows As Long, CallerCols As Long, CallerAddr As String
    Dim RowNdx As Long, ColNdx As Long, v As Variant
    With Application.Caller
        CallerRows = .Rows.Count
        CallerCols = .Columns.Count
    End With
    Dim Result() As Variant: ReDim Result(1 To CallerRows, 1 To CallerCols)
    'fill with result with blank strings
    For RowNdx = 1 To CallerRows
        For ColNdx = 1 To CallerCols
            Result(RowNdx, ColNdx) = ""
        Next ColNdx
    Next RowNdx

    'filter out uniques
    Dim dict As Variant: Set dict = CreateObject("Scripting.Dictionary")
    For Each v In rgList.Cells
        dict(v.Value) = 1
    Next v

    'push uniques to first column of resulting array
    RowNdx = 1
    For Each v In dict.Keys()
        Result(RowNdx, 1) = v
        RowNdx = RowNdx + 1
    Next v
     GetUniques = Result
End Function

1. Enter the formula as shown on the picture below and press CTRL+SHIFT+ENTER:

2. Open the name manager with CTRL+F3 and define a dynamic named range with the following formula =OFFSET(Sheet4!$C$2,0,0,MATCH("*",Sheet4!$C$2:$C$6,-1),1):

3. Use the Dynamic named range as a list for the data validation:

4. works as expected:

Notice : The array formula is not dynamic to the values entered and may need to be updated to match the number of rows when additional rows are added - remember to always press CTRL+SHIFT+ENTER when updating the range.

UDF entered as Array Formula:

Answer 2

Please check my approach for this:

1. Data need not be sorted
2. Supports Many to Many relation
3. No VBA
4. No named ranges
5. Don't need to know unique items names or amount beforehand

OPEN IMAGES IN NEW TAB TO SEE BETTER:

In Sheet1 (The Data sheet):
As formulas:

In Sheet2 (The helper sheet):
As formulas:

In Sheet3 (The result sheet):