Data Validation List with Unique Values Only

[tcustance]

Hello all,

Is there a way to return unique values only in a data validation list that is frequently being refreshed?

Thanks,
Trent

 

[Joe4]

Is there a way to return unique values only in a data validation list that is frequently being refreshed?

Where is the list coming from?
If it is coming from somewhere on a worksheet, you can use Excel's built-in "Remove Duplicates" functionality to remove the duplicates.
Depending on how this list is being edited, you may even be able to automate that process with VBA.

 

[tcustance]

Where is the list coming from?
If it is coming from somewhere on a worksheet, you can use Excel's built-in "Remove Duplicates" functionality to remove the duplicates.
Depending on how this list is being edited, you may even be able to automate that process with VBA.

The list is generated through MS Query. I am using the list for sorting purposes, so I don't want to actually remove the duplicates from my data, i just want to be able to sort using my dropdown of unique results.

Thanks

 

[Joe4]

I am not sure I understand.
How exactly do you use a Data Validation list for sorting purposes?

Have you considered filtering/sorting the list right in the MS Query code, so the data comes across exactly as you need it?

 

[tcustance]

I am not sure I understand.
How exactly do you use a Data Validation list for sorting purposes?

Have you considered filtering/sorting the list right in the MS Query code, so the data comes across exactly as you need it?

I am using MS query to pull all of the data I want to be able to be sorted. I want to pull all of the data because i want to be able to sort it on the sheet (the other people using this sheet aren't familiar with sorting through the MS query window and the sorting will be frequently changed as well). I have another sheet with an summary of all of the data.(i.e. how many orders we have for a certain customer). When I use data validation on the cells, it has all of the duplicates.

Sorry if this is confusing.

 

[Joe4]

Can you make a copy of the data to another sheet where you remove all the duplicates, and use that?

 

[salim hasan]

Supposing your data start from cell(A2) , cell (A1) is the Header
the cell C1 is the target for data validation
try this macro

Option Explicit


Sub Uniq_Data_Val()
Dim i%: i = 2
Dim arr
Dim rg As Object
Dim Last_ro%: Last_ro = Cells(Rows.Count, 1).End(3).Row
Set rg = CreateObject("System.Collections.Arraylist")
With rg
    Do Until i > Last_ro
        If Range("a" & i) <> vbNullString _
             And Not .Contains(Range("a" & i).Value) Then
            .Add Range("a" & i).Value
        End If
      i = i + 1
    Loop
  .Sort
  arr = .toarray
  arr = Join(arr, ",")
 End With
 
 With Range("c1").Validation
 .Delete
 .Add xlValidateList, Formula1:=arr
 End With
End Sub

	A	B	C
Name
Sami
John
Goerge
Albert
Goerge
Salim
Ali
Kamel

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Albert[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​