Remove duplicates in drop list

New Contributor

Hi guys,


I would like to know how to remove duplicates from a drop down list. I would point out that:

     - the source is on another sheet and is growing monthly

     - I don't want to create an intermediate list


Thanks in advance

3 Replies


The source of a data validation drop down list must either be a fixed list of values in the form of a text string (limited to 255 characters), or a range. It cannot be an expression that doesn't evaluate to a range. So I don't think you can do this without an intermediate list.

best response confirmed by Hans Vogelaar (MVP)


The best way to do this at present is with the intermediate list using:


Then refer to the spill using # notation in the data validation menu.  Unfortunately, data validation won't accept UNIQUE so we must make do.  It's interesting that TAKE and DROP can be used in data validation but I don't believe there's a way to use them and only pull unique values if UNIQUE is off the table.

Thanks @Patrick2788 ! You're the boss !