Remove Duplicated items in combo box

i have a combo box in a form from a list of customers

eg

Aston Martin
BMW
BMW
Jaguar
Jaguar

ect.

i want to remove the duplicated entries

so out of the 4 id have

Aston Martin
BMW
Jaguar

the reason for this is that there maybe more than 1 contact in any particular company.

then i want a seperate combobox with all the contacts fot the selected company in it

this will in turn fill in my form
but i think i can do that bit

hope you guys can help

hmm ok that sounds good

thats the kinda thing i ment
(not too good with words lol)

Private Sub UserForm_Initialize()

'sort into order
'by customer
'then contact

    Sheets("Customer Contacts").Activate
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Sheets("customer Contacts").Cells(1, 1).Select
    Sheets("purchase orders").Activate
     
'populate
For i = 2 To Sheets("customer Contacts").UsedRange.Rows.Count + 1


ComboBox1.AddItem (Sheets("customer Contacts").Cells(i, 1))
Next i

End Sub

Good morning FRIEL

John Walkenbach provides a solution to this on his site here. The code supplied also puts all the items in alphabetical order as well, but you can strip this out if you want. I find that using Collection objects are an excellent (and rapid) way of removing duplicates from an array as it doesn't require multiple looping. There is also a demonstration workbook to download.

HTH

DominicB

thanks ill go check it out and ill let you know how it goes

How's this?

sub populate
Range(Range("A1"), Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("P1"), Unique:=True
UserForm1.ComboBox1.RowSource = Range(Range("P2"), Range("P" & Rows.Count).End(xlUp)).Address
UserForm1.Show
end sub

I used column P (fairly arbitrarily) to create a unique filter of column A which I then used as rowsource for your combobox.

HTH

used this

Private Sub UserForm_Initialize()
Dim ND As New Collection

'sort into order
'by customer
'then contact

    Sheets("Customer Contacts").Activate
    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
    Sheets("customer Contacts").Cells(1, 1).Select
    Sheets("purchase orders").Activate
     
'populate
 On Error Resume Next
    For i = 2 To Sheets("customer Contacts").UsedRange.Rows.Count
        ND.Add Sheets("customer Contacts").Cells(i, 1).Value, CStr(Sheets("customer Contacts").Cells(i, 1).Value)
'       Note: the 2nd argument (key) for the Add method must be a string
    Next i
    
    x = 1
    For Each Item In ND
    ComboBox1.AddItem ND.Item(x)
    x = x + 1
    Next Item

End Sub

any ideas on how to do the second part??

i need to say select bmw
and for a second combo box to have the different contacts in it

i think this is going to be the hard bit

Hi FRIEL

Have a look at this workbook with an example userform. I have only done it very simply, so no doubt there are going to be some changes needed, but hopefully it will point you in the right direction.

HTH

DominicB

yea thats good ive played with it n got it to go with what i need
thanks allot

is there a way to reference to a row that the second (contact) value comes from??

as i have numbers and emails on the same row i want to fill in on the form into textboxes

Hi FRIEL

I'd probably stick with the little table, and have a seperate table that lists all your contacts vertically, and their contact details going across. You can read the contents of the combobox like so :

c=ComboBox2.Value

and then perhaps retrieve all the pertinent information using VLookUp.

HTH

DominicB

thanks guys!!

i used this
to use find the row

a = 2
Do While Sheets("Customer Contacts").Cells(a, 1).Value <> ""
If Sheets("Customer Contacts").Cells(a, 1).Value = ComboBox2.Value Then
    If Sheets("Customer Contacts").Cells(a, 4).Value = ComboBox5.Value Then
        GoTo 1
    Else: End If
Else: End If
a = a + 1
Loop
1: