Remove Duplicates in Data Validation List

**NMullis** · 08-30-2012, 04:35 PM

Good day - I would like to use Data Validation to create a unique value drop down list populated from a column with many duplicates. I cannot create an extra column to remove the duplicates, therefore I am looking to do this as part of my Data Validation reference formula. Any ideas?
Thank you in advance for your assistance,
N

**JBeaucaire** · 08-30-2012, 08:46 PM

Originally Posted by NMullis

I cannot create an extra column to remove the duplicates

Sure you can. I give you permission to it the easy way.

1) highlight the column of values
2) Do a Data > Filter > Advanced Filter and use these settings:

-copy to another location (select an empty column)
-unique values only

3) Copy the resulting unique list to a new sheet in the workbook
4) Sort the list
5) Highlight the range of cells that results and give them a name by typing into the NAME box just to the left of the formula bar, where you normally see the active cell address. Let's name this MyList

6) Now hide this reference sheet if necessary
7) Lastly, use that named range as the source of your Data Validation list.

Allow: List
Source: =MyList

**NMullis** · 08-31-2012, 09:01 AM

Thank you Jerry

, but please allow me to restate - creating an extra column is not an option for me in this particular instance. Is there a way to add it to my source reference in Data Validation?
Thanks!

**JBeaucaire** · 08-31-2012, 10:53 AM

Define "automatic" then, for you in this instance. The solution for anything "automatic" is going to be VBA.

1) I push a button and this DV list is updated anew with all the current unique values in column A
2) I type a new value into column A that is not part of the current DV list and it adds itself permanently to the list
3) When I bring this sheet up onscreen each time I would like the DV list to refresh with all the current column A values
4) Some other criteria

==========================
Also, please address a few comments as to why an extra column is not an option for a normal DV list. We deserve to know how that option got removed, right?