Am I trying the impossible?

[Max_power]

Hello,

I was wondering if it is possible to create a list from two columns?
So I have columns product id and product description.
I was wondering if I could validate these into one list in another worksheet. So if someone picks a product they will see the id and the description.

Many thanks

Max

[BigPig]

Hi Max,

There are a few ways to do this. Here's one:

1. 4 columns of data, in a1=Product, b1=ProductID, c1=ProductDesc, d1=IDDesc

2. In the rows underneath put the appropriate info. (except for d2 and
down). I used this info. a2=Pens, Black Ink, a3=Pens, Blue Ink, a4=Pens, Red
Ink. B2 through B4 is 1 to 3. c2=Pens that write in Black Ink, c3=Pens that
write in Blue Ink, and c4=Pens that write in Red Ink.

3. In cell D2 type in:
=B2 & ". " & C2. You can also use concatenate, which essentially does the
same thing.

4. Now in cell A10, go to insert, name, define, type in Product, highlight
the pens in column a, add close.

5. Go to Data, validation, allow list, type in =Product

6. In cell b10 put in:
=VLOOKUP(A10,A1:D4,4)

Hope this answers your question.

[Max_power]

Thank you very much for the detailed response,

I've already used a string concatenation and it doesn't really give me the required result.
The problem is that say if I have text in column A, some text in certain cells is bound to be longer than in other cells and when I concatenate these cells with Column B I get something like

Nike - 12312
Hoe Bloggs - 542353
Mickey Mouse - 324234
Daffy Duck - 132432

I would like to be able to have something like

Nike - 12312
Hoe Bloggs - 542353
Mickey Mouse - 324234
Daffy Duck - 132432


Is there a function or a command that does this.
My method of Concatenation was
=CONCATENATE(R4," ",S3)

Many thanks

Max

[Max_power]

Oops I just seen my reply there, I typed in that they were alligned but it didn't turn out in the post....

[BigPig]

Max,

Does that mean that you answered your own question or...?

Otherwise, the formula you are trying to use won't work as it is. You wrote:
=CONCATENATE(R4,\" \",S3)

Try something like:
=CONCATENATE(R4," \",S3)

And I don't know what your spreadsheet looks like, but in the above formula
you would be asking excel to put the value of r4 and then \ and then the
value of s3. The reason I question that is cell s3 is one cell up and one
cell to the right of r4.

As far as lining up the numbers to a far right edge, why couldn't you leave
them in separate columns, or your answer in separate cells? That would be the
easiest thing to do. Meaning, cell a1 you select the product number, and in
cell b1 shows the Desc, and in c1 shows the id no.

[BigPig]

Hi Max,

This formula works kind of. Assuming that your data is concatenated in
column I with a "." in between and what follows are numbers no longer than 4
digits, then in j1 you could type:
=(LEFT(I1,FIND(".",I1)-1)) & (REPT("
",(((MAX(FIND(".",$I$1),FIND(".",$I$2),FIND(".",$I$3),FIND(".",$I$4),FIND(".",$I$5),FIND(".",$I$6))))-(FIND(".",I1)))+1)) & (RIGHT(I1,4))

What this formula does is takes the text on the left side of the ".",
concatenates it with a number of spaces equal to the largest number of
character spaces that "." is from the left in column I minus the current
cell's number of characters from "." to the left; concatenated with
everything that follows the "." on the right.
The only problem with this is that the amount of 'space' a space would make
is different than what a 'X' would take. So although it does separate the two
pretty good, it's not perfect.

Hope this helps.