Generate list based on criteria

[mleewis]

Hi!

After a few hours of searching for a solution, i hope you are able to help me out. I'm trying to create a list of values so it can be used in a drop down menu.
The problem is that the date from which i want to extract this list has many duplicates. Values may change over time so i want it to be generated like this.

The basic setup from which to retrieve the values is:

DEP NAME
GS V01
GS V02
GS V03
GS V04
GS V05
GS V06
HD D01
HD D02
HD D03
HD D04
HD D05
GS V01
GS V02

What i need is a list build on all values found under NAME on the condition where the DEP is GS. So V01 should only appear once in the list.

I have a feeling i should be doing this with INDEX & MATCH but i haven't been able to generate the results i need. But maybe i'm thinking too difficult.

Thanks!

 

[earthworm]

Check your answer here

https://www.box.com/s/491d0eee9f6aa109548b

 

[mleewis]

Thanks, but how do i get the filters based on the unique values?

I tried the advanced filter on a whole column (B:B), but that doesn't work.

 

[earthworm]

Thanks, but how do i get the filters based on the unique values?

I tried the advanced filter on a whole column (B:B), but that doesn't work.

If you observe carefully i have used the Table instead of normal layout , and table is "Dynamic" in nature . You will only need to add the new value in table list and the drop list will update automatically .

 

[mleewis]

That doesn't seem to work. It doesn't refer to any place specific so i'm not ending up with any values. Could you be more specific?

 

[Tinbendr]

If in fact, you do have a activex combobox on sheet1, then in the Sheet1 code page insert this.
Anytime you update Column B, it will refresh list.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Dim DD As Collection
    Dim LastRow
    Dim aCell As Range
    
    Set DD = New Collection
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    For Each aCell In Range("B2:B" & LastRow)
        'Resume next to make the collection skip dups
        On Error Resume Next
        'Add Name, DEP & Name to create Key.
        DD.Add aCell.Value, aCell.Offset(0, -1).Value & aCell.Value
    Next
    On Error GoTo 0
    With ComboBox1
        .Clear
        For A = 0 To DD.Count
            .AddItem DD(A)
        Next
    End With
End If
End Sub

To use the dropdown value, then

Private Sub ComboBox1_Change()
Range("D1").Value = ComboBox1.Value
End Sub