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)

Please Login or Register  to view this content.

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?

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

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

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 :

Please Login or Register  to view this content.

and then perhaps retrieve all the pertinent information using VLookUp.

HTH

DominicB

thanks guys!!

i used this
to use find the row

Please Login or Register  to view this content.