Hello everybody
I have values in range("A2:A4")
A2: 12,45,78,12,45
A3: 45,78,60,95,16
A4: 12,95,16,53
I need to extract unique values and the result should be like that in cell A5
12,45,78,60,95,16,53
Thanks advanced
Hello everybody
I have values in range("A2:A4")
A2: 12,45,78,12,45
A3: 45,78,60,95,16
A4: 12,95,16,53
I need to extract unique values and the result should be like that in cell A5
12,45,78,60,95,16,53
Thanks advanced
Re: Extract unique values withing a range and the results would be in one cell
Try:-
Sub UnQ()
Dim Rng As Range, Dn As Range, n As Long, Sp As Variant
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
Sp = Split(Dn.Value, ",")
For n = 0# To UBound(Sp)
.Item(Sp(n)) = Empty
Next n
Next Dn
Range("A5").Value = Join(.Keys, ",")
End With
Display More
Re: Extract unique values withing a range and the results would be in one cell
Thanks a lot Mr. MickG for this fantastic code
I'm admired of your solution
Can I have a formula or UDF function that do that task?
Re: Extract unique values withing a range and the results would be in one cell
Try this UDF :-
Run as:- =UnQ("select range require here !!!)
Function UnQ(rng As Range) As String
Dim Dn As Range, n As Long, Sp As Variant
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In rng
Sp = Split(Dn.Value, ",")
For n = 0# To UBound(Sp)
.Item(Sp(n)) = Empty
Next n
Next Dn
UnQ = Join(.Keys, ",")
End With
End Function
Display More
Re: Extract unique values withing a range and the results would be in one cell
Thank you very much for these treasures
You are very helpful
My regards
Re: Extract unique values withing a range and the results would be in one cell
You're welcome
Don’t have an account yet? Register yourself now and be a part of our community!