Solved: Extract distinct list from a range

[YasserKhalil]

Hi
I have values within the range A110 and there are repeated values
I want to extract a list of unique values in the column E

[Bob Phillips]

Have you tried Advanced Filter, Data>Filter>Advanced Filter?

[YasserKhalil]

Yes, I tried Advanced Filter and it doesn't work
The range is

A1:D10

as it doesn't appear in the first post

[mdmackillop]

[vba]
Sub Unique()
Dim r As Range
Dim d, a
Set r = Range("A110")
On Error Resume Next
Set d = CreateObject("Scripting.Dictionary")
For Each cel In r
d.Add CStr(cel), cel
Next
a = d.items
Range("E1").Resize(d.Count) = Application.Transpose(a)
End Sub

[/vba]

[YasserKhalil]

Thanks for help Mr. mdmackillop
But it doesn't work .I don't know why?
Generally here's an attachment with your code ..

[YasserKhalil]

Thanks for help Mr. mdmackillop
But it doesn't work .I don't know why?
Generally here's an attachment with your code ..

[mdmackillop]

Slight change
[VBA]
d.Add CStr(cel), CStr(cel)
[/VBA]

[YasserKhalil]

Perfect ! Excellent
Thanks for your help
Slight change great result
Another slight request my dear
The result contains blank cell .. Could I get rid of that??
Another and last request .. In the column F I want to insert the function that count the times of each value

=Countif(A1:D10;E1)

via code

[mdmackillop]

[VBA]Sub Unique()
Dim r As Range
Dim d, a
Set r = Range("A110")
On Error Resume Next
Set d = CreateObject("Scripting.Dictionary")
For Each cel In r
If cel <> 0 Then d.Add CStr(cel), CStr(cel)
Next
a = d.items
Range("E1").Resize(d.Count) = Application.Transpose(a)
Range("F1").Resize(d.Count).FormulaR1C1 = "=COUNTIF(R1C1:R10C4,RC[-1])"
End Sub
[/VBA]

[YasserKhalil]

Great .. Perfect.. Excellent
Thanks a lot for your help
That's a great forum that has great people

[brettdj]

This is solved but for future reference, the Duplicate Master addin - , http://www.experts-exchange.com/A_2123.html - uses the same approach that Malcolm applied (with the Scripting Dictionary)

It offers the flexibility to manipulate strings when looking for uniques/duplicates (case sensitivity, white space handling, string transformations)

Cheers

Dave

[YasserKhalil]

Great addin Mr. Dave
I like it very much