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.