Unique/Distinct Dropdown List

[Shawn]

Does anyone know of a way to create any type of dropdown list (i.e. Data
Validation List, Combo Box, etc.) which would contain a list of unique values
from a range of data? For example, if A5:A10 contained the names Mark, Larry,
Larry, John, Chris, John the dropdown would contain Mark, Larry, John, Chris.

One solution I thought of was to use one of the various methods (autofilter,
array, UDF) to first create the unique list, then use the unique list as the
range for the dropdown list, but I was wondering if it was possible to skip
creating the unique list first and have it appear only in the dropdown.

I am open to excel formulas or VBA. Let me know if anyone has any ideas.

 

[Max]

One way using formulas and a dynamic named range

Assume source data will be entered in Sheet1, in A1 down
In say, Sheet2,
In A1:
=IF(Sheet1!A1="","",IF(COUNTIF(Sheet1!A$1:A1,Sheet1!A1)>1,"",ROW()))

In B1:
=INDEX(Sheet1!A:A,SMALL(A:A,ROW()))
Copy A1:B1 down to cover the max expected extent of source data in Sheet1,
say down to B100. Don't worry about the #NUM! errors in col B.

Then create a defined name (via Insert>Name>Define)
Name: MyR
Refers to:
=OFFSET(Sheet2!$B$1,,,SUMPRODUCT(--NOT(ISERROR(Sheet2!$B$1:$B$100))))

You're all set. In any sheet, you can now create a DV using Allow>List,
Source: =MyR. That DV will give you the required dynamic list of uniques
(from Sheet1's col A) in its droplist, all neatly packed at the top.

Success? Celebrate it, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[OssieMac]

Hi Shawn,

Suggest you test in a blank workbook first and see if it does what you want
and then adapt to your project.

Assume your validation drop down will be in cell A1 on Sheet1.

Assume the list with multiple occurrences of names is in column A of Sheet2
with the column header in A1. (Column must have a column header to use with
Advanced filter.)

To install test:

Select Sheet2
Select cell A1 and insert a column header.
Copy a list of your names into column A under the header.
Select cell B2.
Define a name for cell B2 (Name must be MyDropList) You can edit the code if
you want to change that.

Select Sheet1 cell A1
Set up conditional dropdown with =MyDropList as the list range.

Right click the Sheet1 name tab and select View code.
Copy the following code into the VBA editor.

Whenever Cell A1 on Sheet1 is selected, the unique list is updated for the
dropdown. (If you change to sheet 2 you will see the unique list in column B.)

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
'Data list in column A of sheet2
'Unique dropdown list created in column B
Dim rngDropDown As Range

'Edit "A1" to the dropdown cell
If Target = Range("A1") Then
With Sheets("Sheet2")
.Columns(2).Clear
.Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp)) _
.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Cells(1, 2), _
Unique:=True

Set rngDropDown = .Range(.Cells(2, 2), _
.Cells(.Rows.Count, 2).End(xlUp))

'Include code between asterisks _
'if dropdown list to be sorted
'***************************************
.Sort.SortFields.Add Key:=rngDropDown, _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange rngDropDown
.Header = xlNo
.Apply
End With
'*************************************

rngDropDown.Name = "MyDropList"
End With

End If
End Sub

 

[Dave]

Hi Shawn,
I've used the following rather long one-liner with good success.
It's an event code that builds a unique list from a master list, adding to
the unique list automatically as necessary.
Master list starts in A1, and can be as long as you like.
The unique list is created, starting at D2.
You can use the D2 list for your data validation drop-down source.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Range(Cells(1, 1), Cells(Cells(Rows.Count,
1).End(xlUp).Row + 1, 1)).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("D2"), Unique:=True
End Sub

This forum window wraps the code, but it all goes on one line.

Perhaps you will find this useful.
Regards - Dave.

 

[OssieMac]

Hi again shawn,

If you decide to use code then replace my code with the following. Found a
couple of problems. (1st error if user selects multiple cells, 2nd error
forgot to clear existing sort info.)

Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
'Data list in column A of sheet2
'Unique dropdown list created in column B
Dim rngDropDown As Range

'Multiple cell selection will return an error
If Target.Cells.Count > 1 Then Exit Sub

'Edit "A1" to the dropdown cell
If Target = Range("A1") Then
With Sheets("Sheet2")
.Columns(2).Clear
.Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp)) _
.AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=.Cells(1, 2), _
Unique:=True

Set rngDropDown = .Range(.Cells(2, 2), _
.Cells(.Rows.Count, 2).End(xlUp))

'Include code between asterisks _
'if dropdown list to be sorted
'***************************************
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=rngDropDown, _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
With .Sort
.SetRange rngDropDown
.Header = xlNo
.Apply
End With
'*************************************

rngDropDown.Name = "MyDropList"
End With

End If
Set rngDropDown = Nothing
End Sub

 

[Dave]

Hi
Forgot to mention that the list starting in A1 needs a header
Regards - Dave.

 

[Max]

Dave, its a good one. Could the code be enhanced a little to remove the
blank space which appears within the results range in col D should there be
any intervening blank cells within the source data entered in col A? Thanks

Hi Shawn,
I've used the following rather long one-liner with good success.
It's an event code that builds a unique list from a master list, adding to
the unique list automatically as necessary.
Master list starts in A1, and can be as long as you like.
The unique list is created, starting at D2.
You can use the D2 list for your data validation drop-down source.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Range(Cells(1, 1), Cells(Cells(Rows.Count,
1).End(xlUp).Row + 1, 1)).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("D2"), Unique:=True
End Sub

This forum window wraps the code, but it all goes on one line.

Perhaps you will find this useful.
Regards - Dave.

..

 

[Dave]

Hi Max,
Haven't thought about the blank, but I guess you could add the following line:

Range(Cells(3, 4), Cells(Cells(Rows.Count, 4).End(xlUp).Row + 1, 1)).Sort
Key1:=Range("D3")

It will sort the unique list each time, placing the blank cell at the end
where it won't be recognized by a dynamic named range.

Regards - Dave.

 

[Max]

Thanks, Dave. Works well.
Max

 

[Dave]

You're welcome

Thanks, Dave. Works well.
Max

 

[Shawn]

Thanks for all of the useful suggestions. I ended up using a UDF to return an
array of unique values on another tab (which excludes blanks), named the
range, and used the named range in the dropdown validation list. I guess
there is no way to avoid having the unique list somewhere in the workbook.