Unique names

[Romulo Avila]

Good afternoon,

I need to fill cell D2 with unique values ​​in RANGE (A2: A20), it can be VBA or formula.



Thank you for your help


RomuloRDM

[Logit]

.
Paste this formula in D2 :

=INDEX($A$2:$A$20,RANDBETWEEN(1,COUNTA($A$2:$A$20)),1)

You can update the random selection by pressing F9

[Romulo Avila]

Good afternoon,


I think I did not express clearly, what I need and that in cell D2 the names appear comma-separated.
Example:
Antonio, Pedro, Joaquim, Geraldo, Jose ..... and do not leave repeated.


Thanks and sorry..

[Leith Ross]

Hello Romulo,

This macro is a User Defined Function. It works like a Formula. The code below has been added to the attached workbook.

Public Dict As Object


Function GetUniqueNames(SrcRng As Range) As String


    Dim Data    As Variant
    Dim Key     As Variant
    
        Application.Volatile
        
        If Dict Is Nothing Then
            Set Dict = CreateObject("Scripting.Dictionary")
            Dict.CompareMode = vbTextCompare
        End If
            
            Dict.RemoveAll
            
            Data = SrcRng.Value
            
            For Each Key In Data
                Key = Trim(Key)
                If Key <> "" Then
                    If Not Dict.Exists(Key) Then
                        Dict.Add Key, Key
                    End If
                End If
            Next Key
            
        GetUniqueNames = Join(Dict.Items, ",")
        
End Function

[Romulo Avila]

Good evening,


Many thanks to all, Leith Ross was very good, thanks for the support.


Att.