Excel - removing duplicates with data validation

Question

I know this issue may sound like something that has been solved a million times, however all of the fixes that I have found haven't helped.

I have a horizontal cell range of numbers which I want to be displayed in a data validation list. Easily enough I can just set the DV list as =A1:H1. My issue is that I would then like to remove any duplicate values without creating any new lists of data.

Any ideas?

You could use VBA to do that, but I'm not sure if it can be achieved without VBA — Raunak Thomas
– Raunak Thomas, Commented Feb 12, 2018 at 15:38
I found this link that might be interesting, but honestly speaking, I didn't understand how it works. mrexcel.com/forum/excel-questions/… — Raunak Thomas
– Raunak Thomas, Commented Feb 12, 2018 at 15:56

Gary's Student · Accepted Answer · 2018-02-12 15:53:06Z

2

Select the cells you want the DV to be applied and run this short macro:

Sub uniDV()
    Dim r As Range
        For Each r In Selection
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:=unik(Range("A1:H1"))
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        Next r
End Sub

Public Function unik(rng As Range) As String
    Dim c As Collection, r As Range
    Set c = New Collection
    On Error Resume Next
    For Each r In rng
        v = r.Value
            c.Add v, CStr(v)
        If Err.Number = 0 Then
            unik = unik & "," & v
        Else
            Err.Number = 0
        End If
    Next r
    On Error GoTo 0
    unik = Mid(unik, 2)
End Function

The function constructs a comma-separated string and the sub applies the DV.

answered Feb 12, 2018 at 15:53

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jack Campion Over a year ago

If an in-built function is not available then I will be looking for something like this however it is for multiple ranges of data. I would be looking for something that said "For each cell in range, create data validation list in [specified cell] consisting of unique data from [specified range].. Is this possible?

Gary's Student Over a year ago

@JackCampion It is possible.............we wouuld need to know which ranges to apply the DV and which ranges contain the DV lists.

Collectives™ on Stack Overflow

Excel - removing duplicates with data validation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related