Forum Discussion
Solved
Remove duplicates in drop list
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 🙂
The best way to do this at present is with the intermediate list using:
=SORT(UNIQUE(range))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.
The best way to do this at present is with the intermediate list using:
=SORT(UNIQUE(range))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 !
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.
