User Form Help Seems Simple

Brandon Johnson · 06-05-2006, 05:20 PM

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 · 06-05-2006, 05:45 PM

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" <[email protected]> wrote in message
news:[email protected]...
>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
> .
> .
> .
> and so on.
>

Brandon Johnson · 06-06-2006, 09:50 AM

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 wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> > .
> > .
> > .
> > and so on.
> >

Chip Pearson · 06-06-2006, 09:55 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> > .
>> > .
>> > .
>> > and so on.
>> >
>

Brandon Johnson · 06-06-2006, 10:20 AM

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?

Chip Pearson wrote:
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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
> >> > .
> >> > .
> >> > .
> >> > and so on.
> >> >
> >

Brandon Johnson · 06-06-2006, 10:35 AM

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?

Brandon Johnson wrote:
> 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?
>
> Chip Pearson wrote:
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > >> 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" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >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
> > >> > .
> > >> > .
> > >> > .
> > >> > and so on.
> > >> >
> > >