listbox multicolumn without duplicates

JPDO

Board Regular
Joined
Apr 10, 2002
Messages
140
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JPDO

Board Regular
Joined
Apr 10, 2002
Messages
140
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

Active Member
Joined
Aug 21, 2002
Messages
255
put in C1:
=TEXT(A1;"00000") & " " & B1
drag this downwards.

As I said: use column C in your box
 

technix

Active Member
Joined
May 19, 2002
Messages
326
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,306
Members
412,716
Latest member
thviid
Top