Show repeating values once in data validation drop down list

[DashboardNovice]

In the attached file, in cell H3, I have a data validation drop down list. This references a named range which has repeating values. In the data validation drop down, I would like for duplicate values to be shown only once.

Is there a way to do this?

 

[Chihiro]

You should just create intermediate list. Use formula to extract unique values from named range. Then use that as your source for dropdown.

There are many ways to extract unique values from list. Try one of the below.
https://www.extendoffice.com/documents/excel/4032-excel-dynamic-list-of-unique-values.html
https://www.ablebits.com/office-addins-blog/2016/04/21/get-list-unique-values-excel/

 

[Peter Bartholomew]

A way that will soon be available to Office 365 users is to use the new(ish) array function
= UNIQUE(list)
This can be tidied up a bit more using
= SORT( UNIQUE(list) )
If the anchor cell of the resulting spilt array were 'validate', then
=validate#
will work as a sorted, dynamic validation list.

 

[salim hasan]

Try this macro

Option Explicit

Sub data_val()
Dim My_list As Object
Dim Rg As Range, CL As Range

 Set Rg = Range("C3", Range("c2").End(4))
 Set My_list = CreateObject("System.Collections.ArrayList")

With My_list
  For Each CL In Rg
    If Not .Contains(CL.Value) Then .Add CL.Value
  Next
End With
    
With Cells(2, "H").Validation
    .Delete
    .Add 3, Formula1:=Join(My_list.ToArray, ",")
End With

Set My_list = Nothing
End Sub