Need UNIQUE values for Data Validation List

[jg]

I have many columns, one of which is DIVISION with about 500 values
(rows.) The data looks like this...

DIVISION
40 - 49
20 - 29
40 - 49
20 - 29
40 - 49
50 +
50 +
etc....

I need a cell with a drop down menu where the only items displayed are
unique values from DIVISION. Currently Data-Validation-List sort of
works but it will list all 500 values. I only want the unique values.


Thanx for all who help.

jg.

================================================

Ultimately, what I am trying to accomplish is to give the user the
ability to select any value from the DIVISION column and have a small
table populated from matching rows. Autofilter does this but it does
it with the entire sheet. I want to have more control.

 

[Kletcho]

Try using advanced filters. Check the box that says "unique only" and
use copy. I usually put the location somewhere hidden or on a separate
worksheet (if you want to use data validation with data from another
worksheet you must use a named range). If you need the list to always
remain up to date you will need to put some VBA code in.

 

[jg]

Yes, I will need the list to always be up to date. Can you point me in
the right direction. In the mean time I'll revisit the "Advanced
Filters"

 

[jg]

Yes, I will need the list to always be up to date. Can you point me in
the right direction. In the mean time I'll revisit the "Advanced
Filters"

Advanced filters is not going to work. My data looks like this...

Gender Category Division
----------------------------------------------
M Novice 40 - 49
F Expert 20 - 29
F Novice 50+

etc... to 500 rows.

Ultimatelly what I need is a small table for the top 5 in each
Gender/Category/Division combination. The data changes on a weekly
basis along with the values. Some weeks, Division will be broken by 10
(20-29) other it will be broken down by 5 (20-24). So the values I use
for my top 5 categories has to by dynamic as well.

I am not afraid to do this in VB, however, I do not even know where to
begin. I have not programmed in VB in close to 8 years. Never with
Excel though.

Any help greatly appreciated.

 

[Kletcho]

Could use have a formula that controls the division ranges (maybe by
changing a value in a cell at the top). If you could do that then you
could use a pivot table to easily get totals. If not I can point you
in the right direction with VBA.

 

[jg]

Yes, please point me in the right direction using VBA. I would rather
have the flexibility of using code than the limited built in formulas.

Thanx.

 

[Kletcho]

You would use this to get unique items:

Range("E3:E27").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("I3"), Unique:=True

Where "E3:E27" contains your division list including the the header
containing "Division" and "I3" contains the word "Division". This code
will fil the unique values below "I3". You could then place this line
of code in a worksheet activate event, a change event, or a workbook
open event. Then you can have the reference for the data validation
list point to "I4:I..whatever the length is (to determine the length
automatically do a search on dynamic named ranges).