Unique Validation List

**aftabn10** · 05-14-2009, 07:42 AM

Hi, I was wondering if their is any way where I could display only Unique Values within a Validation List. The source data has duplicate values but rather than selecting Unique Values I was hoping their might be a way to display this.

I have attached a sample worksheet, if somebody could please help.

Thanks in advance.

**JONvdHeyden** · 05-14-2009, 08:20 AM

Nope, you're going to have to create a list of unique items I'm afraid. Even if you created a list of non-contiguous cells (e.g. $B$2,$B$4:$B$5,$B$8), you still wouldn't be able to use it as a validation list source. It will only accept contiguous ranges.

For help on preparing unique lists see here: http://www.contextures.com/xladvfilter01.html#FilterUR

**MickG** · 05-14-2009, 08:36 AM

Hi, If you use advanced Filter, "Unique values" & select "Copy to another location", you can then use this new range for you "Validation List"
Regards Mick

**aftabn10** · 05-14-2009, 08:40 AM

Thanks for your response JONvdHeyden and MickG. Ideally I dont want to create another list of unique items to then select as my range as I was looking to automate this process.

I have come across the following:

http://www.cpearson.com/excel/NoDupEntry.aspx

which uses a Custom Criteria with the following formula but this doesn't actually work so I dont know if this was using Excel 2007 rather than the 1997 that has been quoted on the URL page.

I dont know if its possible to do something similar that would do the job.

**martindwilson** · 05-14-2009, 08:50 AM

you can validate against a pivot table and use a dynamic named range in the validation
refresh pivot when new data entered.
right click in pivot table things area select field hide blank
right click table
options uncheck grand totals
see named range "martin" for formula for dynamic range
dropdown is in c1

**JONvdHeyden** · 05-14-2009, 08:57 AM

Originally Posted by aftabn10

Thanks for your response JONvdHeyden and MickG. Ideally I dont want to create another list of unique items to then select as my range as I was looking to automate this process.

I have come across the following:

http://www.cpearson.com/excel/NoDupEntry.aspx

which uses a Custom Criteria with the following formula but this doesn't actually work so I dont know if this was using Excel 2007 rather than the 1997 that has been quoted on the URL page.

I dont know if its possible to do something similar that would do the job.

That article doesn't describe a method of how to create a validation list, it shows how to prevent duplicate entries from being added to a list. The COUNTIF method is valid in 2007 too.

Martins suggestion of a pivot table is valid, but the truth is that you will need to build a unique list if you want to create a list of unique items in a validation list. Validation lists will not accept non-contiguous ranges or array constants so we couldn't even use a UDF to return a range of unique items. Sorry.

**contaminated** · 05-14-2009, 09:48 AM

Using this array formula u can create unique values

=IF(ISERROR(INDEX(A1:A29,SMALL(IF(IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))<>ROW(INDIRECT("1:"&ROWS(1:29))),"",IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))),ROW(INDIRECT("1:"&ROWS(1:29)))))),"",INDEX(A1:A29,SMALL(IF(IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))<>ROW(INDIRECT("1:"&ROWS(1:29))),"",IF(ISERROR(MATCH(A1:A29,A1:A29,0)),"",MATCH(A1:A29,A1:A29,0))),ROW(INDIRECT("1:"&ROWS(1:29))))))

See attached file pls..
But this formula is too long...

Hope it helps