Repeat Lines in A validated list

[deante]

I am trying to create a form using excel that uses a validated list. The list comes from a named range that is from a data set that is maintained in the same workbook. The problem is the data worksheet has repeat values.

Like This

Operation
111
112
111
112
115
116
112

I would like the drop down list to only include 111,112,115, and 116. I cannot type in the values because there are too many an because new operations are constantly being added. I would like to create macros based on which selection the user has made.

Thanks in Advance

 

[xlbo]

you cannot restrict data validation lists if there are duplicate values

you must create a list with unique values - the easiest way to do this is to use the ADVANCED filter, ticking the option for unique value and for copying to a new location - then base your validation list of this new dataset

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

You can create a unique list fairly easily with an additional column and a formula.

You'll need to define a few named ranges for this to work as well. To do so, hit Ctrl + F3, enter these names...

BigNum
Refers To:
=9.999999E+307

FullList
Refers To:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(BigNum,Sheet1!$A:$A))

Uniques
Refers To:
=Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(BigNum,Sheet1!$B:$B))

In this example, all the data lies on Sheet1, list is in column A and a header in A1, a (textual) header for uniques in B1 and the following formula in B2 ...

=IF(ISNA(MATCH(0,COUNTIF($B$1:B1,FullList),0)),"",INDEX(FullList,MATCH(0,COUNTIF($B$1:B1,FullList),0)))

This formula is confirmed with Ctrl + Shift + Enter as it's an array formula. Copy this formula down as far as you think you'll need, then add some rows for expansion purposes.

The only real problem with this is that you don't always know how far to copy the formula down and if you don't have it copied down far enough, you'll be cutting some values out of your list. You *could* combat this with VBA, but it would be a short distance farther to just code the whole thing. Preperation and prevention are key here.

Finally, for a data validation of your unique list, select the cell(s) in question, Data | Validation | List | Source: =Uniques.


HTH

-----------
Regards,
Zack Barresse