How to display data in Combobox with no dups

[Oreg]

I have a large amount of data in columns A, B, & C in sheets("LOC").
Columns A & B have many duplicate entries, which I do not want to
delete. Combobox1 displays column A data. Combobox2 displays column
B. Currently, with the code below, I can make a selection in Combobox1
and it will auto populate Combobox2 with the matching data (in relation
to what's been selected in Combobox1).

My trouble is that I do not want to see duplicates entries in either
Comboboxes when I select the drop down button if that is possible.

Here's my code so far.....Thank you !

Private Sub Combobox1_click()
Dim sData As String
sData = Combobox1.Value
Combobox2.RowSource = ""
Combobox2.Clear
For Each cell In Worksheets("LOC").Range("A2:A3908")
If LCase(cell.Offset(0, 1).Value) = LCase(sData) Then
Combobox2.AddItem cell.Value
End If
Next
End Sub


Oreg

 

[Tom Ogilvy]

http://www.j-walk.com/ss/excel/tips/tip47.htm

from John Walkenbach's site should get you started.

 

[Oreg]

Hi Tom,

Thanks for the help. I tried your suggestion and it works fine, but i
takes a minute or two to perform. I came across another solutio
(below), which takes no time to complete. Still running into a proble
that both solutions seem to have.

When combobox1 is dropped down, sure enough...no dups...working grea
so far ! I dropped down combobox2 and it populates, but not with al
of the selections that I was hoping for. For example. Column A has
cells whos value is "Buffalo". In column B (next to the 5 "Buffalos
is Leona, Evans, Fairfield, Darien Center, and Buffalo Js. When I dro
down Combobox2, I only see Darien Center, Evans and Leona. It seems t
be dropping, or not detecting, the other 2 cells?? Here's the code I'
using. Any ideas or suggestions would be greatly appreciated !!!

Private Sub UserForm_Initialize()
Dim c As Range, D As Object
Set D = CreateObject("Scripting.Dictionary")
For Each c In Sheets("LOC").Range("A2:A3908")
If Not D.Exists(c.Value) Then D.Add c.Value, 1
Next c
regenA.List = Application.Transpose(D.Keys)
End Sub


Private Sub Combobox1_click()
Dim sData As String
sData = regenA.Value
Combobox2.RowSource = ""
Combobox2.Clear
For Each Cell In Worksheets("LOC").Range("A2:A3908")
If LCase(Cell.Offset(0, 1).Value) = LCase(sData) Then
Combobox2.AddItem Cell.Value
End If
Next
End Sub


Ore