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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.