Remove Duplicated items in combo box

[FRIEL]

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

[Cheeky Charlie]

Hi, I think the answer is not to remove the duplicates but instead only to insert unique entries.

Code you post the code which populates your combobox?

[FRIEL]

hmm ok that sounds good

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

Please Login or Register  to view this content.

[dominicb]

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

[FRIEL]

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

[Cheeky Charlie]

How's this?

Please Login or Register  to view this content.

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

HTH

[FRIEL]

used this

Please Login or Register  to view this content.

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

[dominicb]

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

[FRIEL]

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

[dominicb]

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 :

Please Login or Register  to view this content.

and then perhaps retrieve all the pertinent information using VLookUp.

HTH

DominicB

[FRIEL]

thanks guys!!

i used this
to use find the row

Please Login or Register  to view this content.

[norulen]

How's this?

Please Login or Register  to view this content.

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

HTH

Superb Code Worked Really well for me