Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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" wrote in message ups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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" wrote in message ups.com... 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" wrote in message ups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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" wrote in message ups.com... 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" wrote in message ups.com... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Help Seems Simple
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" wrote in message ups.com... 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" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking to create a simple user form with lookup | Excel Discussion (Misc queries) | |||
User Form Interface .... really a simple question! | Excel Programming | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
Use a simple Drop Down List or User Form | Excel Programming |