Dropdown cell containing only the unique values from a column

[Seansan]

Hi there,

I have been looking for an answer on this one for some time now:

How can I create a Dropdown cell containing only the unique values from a
column (without using extra cells, but using vba is ceratinly an option). Is
it maybe an idea to create a module/macro that can filter a list and return
the unique items separated by a semi-colon?

thx, Seansan

 

[Frank Kabel]

Hi
quite difficult without using a helper column. You may use the
following function which returns an array of unique values for a
specified range (posted by Myrna Larson some time ago):
Function MakeArray(rng As Range) As Variant
Dim Ary As Variant
Dim col_index As Long
Dim Pos As Long
Dim row_index As Long
Dim v As Variant
Dim x As Variant

ReDim v(1 To rng.Cells.Count)
Pos = 0
Ary = rng.Value
For row_index = 1 To UBound(Ary, 1)
For col_index = 1 To UBound(Ary, 2)
x = Ary(row_index, col_index)
If IsEmpty(x) = False Then
If IsError(Application.Match(x, v, 0)) Then
Pos = Pos + 1
v(Pos) = x
End If
End If
Next col_index
Next row_index
ReDim Preserve v(1 To Pos)
MakeArray = v
End Function

 

[Seansan]

Thx Frank, but ow do I use this formula to get the unique values in my
validation list? Excel doesnt allow custom formulas in validation

Seansan

 

[Frank Kabel]

Hi
for your data validation list: no chance but using a helper column