Unique/Distinct Dropdown List
 

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.

Unique/Distinct Dropdown List
 

Shawn,
First of all, use the "name" function to give range A5:A10 a name.
In this example, assume the name given is 'ListA'. Then, under data
validation choose "List" and under "Source" key in "=ListA". Then whenever
you change the entries within A5:A10, the drop down list item will change
accordingly.
Please advise if this works for you.


Shawn wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

Unique/Distinct Dropdown List
 

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,Sheet 1!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 InsertNameDefine)
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 AllowList,
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
---
"Shawn" wrote:
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.

Unique/Distinct Dropdown List
 

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.

"Shawn" wrote:

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.

Unique/Distinct Dropdown List
 

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

"Dave" wrote:

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.

"Shawn" wrote:

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.

Unique/Distinct Dropdown List
 

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

"Dave" wrote in message
...
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.
..

Unique/Distinct Dropdown List
 

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" wrote:

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

"Dave" wrote in message
...
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.
..

Unique/Distinct Dropdown List
 

Thanks, Dave. Works well.
Max

"Dave" wrote in message
...
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.

Unique/Distinct Dropdown List
 

You're welcome

"Max" wrote:

Thanks, Dave. Works well.
Max

"Dave" wrote in message
...
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.

Unique/Distinct Dropdown List
 

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.

"Max" wrote:

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,Sheet 1!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 InsertNameDefine)
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 AllowList,
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
---
"Shawn" wrote:
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.