User Form Help Seems Simple

[Brandon Johnson]

i have a column of duplicate text values. i want to extract the values
and have a compact list of not duplicated values. is there any way to
do this. example:

(at moment) (want)
rgn rgn
north North
east East
east South
east West
north
south
south
east
west
west
south

 

[Chip Pearson]

Try something like the following:

Private Sub CommandButton1_Click()
Dim Dict As Object
Dim Rng As Range
Dim V As Variant
Set Dict = CreateObject("Scripting.Dictionary")
For Each Rng In Range("A1:A20") '<<< CHANGE
If Dict.exists(Rng.Text) = False Then
Dict.Add key:=Rng.Text, Item:=Rng.Text
End If
Next Rng
With Me.ListBox1
.Clear
For Each V In Dict.items
.AddItem V
Next V
End With
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Brandon Johnson]

can you explain your code alittle. this is like literally my first
experience with vba in excel. i usually use access. but this project is
different. thankyou much.

 

[Chip Pearson]

Brandon,

The first thing the code does is create a Dictionary object. This
is similar to a Collection object but is much faster and has more
features. A Dictionary is a set of paired objects: the object
itself (any variable type) and an associated key (a unique
string). See help for more details about a Dictionary.

Then the code loops through range A1:A20. For each cell in this
range, we call the Exists method of the Dictionary to determine
if a key matching the range text exists. If Exists returns True,
a key with that value already exists in the Dictionary and so we
do nothing. If Exists returns False, no key matching the cell
value exists in the Dictionary, so we add it. In this case, both
the item and the key are the same.

At the end of the loop, we have a Dictionary containing the
unique values from A1:A20. Then, we loop through the Items of the
Dictionary, adding each one to the listbox.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Brandon Johnson]

oh wow thats a niffty little function. Now if i wanted to present those
results to a spreadsheet. how would i go about doing that, if you dont
mind?

 

[Brandon Johnson]

Reason being that i want to do that is cuz ultimatly in the end i want
to be able to have 2 cbo's and a check box and a button. All refering
to a spreadsheet. i hard coded the first cbo values with the categorys
that i want to search. A.K.A columns. with that the second cbo will
populate with the distinct values from the first cbo criteria column so
they can be like ok, i want to search for upcs, then i want to search
for either all the upcs or just one. so they either select the upc that
they want to search or they click the check box that will search all
the upcs. i hope this makes sense thus far. then when you click button
i want the results from waht you picked to display all the data
corrisponding to that. any ideas?