PDA

View Full Version : Solved: Extract distinct list from a range



YasserKhalil
06-24-2010, 05:28 AM
Hi
I have values within the range A1:D10 and there are repeated values
I want to extract a list of unique values in the column E

Bob Phillips
06-24-2010, 06:31 AM
Have you tried Advanced Filter, Data>Filter>Advanced Filter?

YasserKhalil
06-24-2010, 10:50 AM
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
06-24-2010, 11:10 AM
Sub Unique()
Dim r As Range
Dim d, a
Set r = Range("A1:D10")
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

YasserKhalil
06-24-2010, 11:39 AM
Thanks for help Mr. mdmackillop
But it doesn't work .I don't know why?
Generally here's an attachment with your code ..

YasserKhalil
06-24-2010, 11:40 AM
Thanks for help Mr. mdmackillop
But it doesn't work .I don't know why?
Generally here's an attachment with your code ..

mdmackillop
06-24-2010, 11:49 AM
Slight change

d.Add CStr(cel), CStr(cel)

YasserKhalil
06-24-2010, 12:34 PM
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
06-24-2010, 01:53 PM
Sub Unique()
Dim r As Range
Dim d, a
Set r = Range("A1:D10")
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

YasserKhalil
06-24-2010, 03:15 PM
Great .. Perfect.. Excellent
Thanks a lot for your help
That's a great forum that has great people

brettdj
07-03-2010, 09:51 PM
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
07-04-2010, 01:10 AM
Great addin Mr. Dave
I like it very much