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.

--
Regards,
Tom Ogilvy


"Oreg" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> Oreg
> ------------------------------------------------------------------------
> Oreg's Profile:
http://www.excelforum.com/member.php...fo&userid=9195
> View this thread: http://www.excelforum.com/showthread...hreadid=475420
>

[Oreg]

Hi Tom,

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

When combobox1 is dropped down, sure enough...no dups...working great so far ! I dropped down combobox2 and it populates, but not with all of the selections that I was hoping for. For example. Column A has 5 cells whos value is "Buffalo". In column B (next to the 5 "Buffalos" is Leona, Evans, Fairfield, Darien Center, and Buffalo Js. When I drop down Combobox2, I only see Darien Center, Evans and Leona. It seems to be dropping, or not detecting, the other 2 cells?? Here's the code I'm 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


Oreg