Forum Discussion

Toodz's avatar
Toodz
Copper Contributor
Sep 21, 2022
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 ...
excel
office 365
  • Patrick2788's avatar
    Patrick2788
    Sep 21, 2022

    Toodz 

    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.

Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 21, 2022

Toodz 

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.

Toodz's avatar
Toodz
Copper Contributor
Sep 21, 2022

Thanks Patrick2788 ! You're the boss ! 

Resources