listbox multicolumn without duplicates

[JPDO]

Hi,

I have a problem using VBA-code to fill up a multicolumn listbox with in the first column the customer number and in the second column the corresponding customer's name. I use MicroSoft Excel 97.

To do this I have a spreadsheet with, let's say, 2 columns where the A-column contains th customer number and the B-column contains the corresponding customer name. BUT the data is not sorted and not unique. So there are duplicate combinations of customer number and names.
I want to have a listbox representing in ascending order the customer number and its corresponding name on one line-item.
In the book "Excel 2002 Power Programming with VBA" by J. Walkenbach I found an example working with 1 column, but I can not figure out to make this working with 2 columns.

Can someone provide me a VBA-macro code solving this ?

Thanks in advance for any help.

 

[rikrak]

Hi,

Take a look at http://www.mrexcel.com/board/viewtopic.php?topic=22052&forum=2

To remove duplicates there are many ways. Just search the board.

 

[JPDO]

Hi,
Thanks for your response.
I found this as answer:

You can use another column to concatenate the contents of the other 2.
columns A&B you want to see.
Put in C1 =A1&" "&B1 to concatenate A1&B1
Use column C in your list

But my customernumber is at maximum 5 characters of lenght; Customer name has a lenght of 100 characters.

I want to have concatenations of a fixed lenght of 5 char + 1 blank + fixed lenght of 100 for the name.
I will avoid to have entries like:
"5 aaaa"
"10 bbbb"
"55555 cccc"
I like to have:
"5 aaaaa"
"10 bbbbbbbb"
"55555 ccccccccccc"
How do i load the separate columns in the listbox in VBA ?
This message was edited by JPDO on 2002-09-15 08:50

 

[rikrak]

put in C1:
=TEXT(A1;"00000") & " " & B1
drag this downwards.

As I said: use column C in your box

 

[technix]

One way of loading the data into the listbox is to do the following

lets use a1 and b1

in a1 enter "Customer Number" without the ""
in b1 enter "Customer Name" again without the ""

now in a2 till a whatever and b2 - whatever fill your data

now name your data select a1:bwhatever and name it data or smething

now in your userform's listbox select column count 2
column heads true
column widths 50,100 play around with this
rowsource sheet1!data

This will then load your lists in the listbox and be seperated in two columns

HTH

Derrick