Am I trying the impossible?

M

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 anothe
worksheet. So if someone picks a product they will see the id and th
description.

Many thanks

Ma
 
B

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.
 
P

Pete_UK

If your data is in columns A and B, then this formula in C1 will join
them together with a hyphen in between:

=A1&" - "&B1

Copy this down the column by double-clicking the fill handle (the small
black square in the lower right corner of the cursor). While these
formulae are still highlighted, click <copy> then Edit | Paste Special
| Values (check) OK and <Esc> to fix the values. Then this combined
column can be moved elsewhere to set up your validation list.

Hope this helps.

Pete
Hope this helps.
 
M

Max_power

Thank you very much for the detailed response,

I've already used a string concatenation and it doesn't really give m
the required result.
The problem is that say if I have text in column A, some text i
certain cells is bound to be longer than in other cells and when
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

Ma
 
M

Max_power

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

Pete_UK

If you want a fully dynamic solution, then you need to find the maximum
lengths of the data in columns A and B, so in two helper columns (eg
columns W and X) put in these formulae:

W1: =LEN(A1)
X1: =LEN(B1)

and copy these down for as many rows as you have in your data. Then
enter these formulae in the cells stated:

Y1: =MAX(A1:A100)
Z1: =MAX(B1:B100)

I have assumed you have 100 rows of data - adjust to suit.

Then in C1 you can enter this formula:

=A1&" - "&REPT(" ",$Y$1+$Z$1-LEN(A1)-LEN(B1))&B1

You will see the effect better if you choose a non-proportional font
such as Courier. Then you can copy this formula down and you will have
your text aligned as you indicated. You can fix the values as I
described in my earlier post, and then move the data onto another
sheet. Columns W - Z could then be deleted.

Hope this helps.

Pete
 
B

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.
 
P

Pete_UK

On further reflection, if you want to use this in a drop-down list, I'm
not sure if you can change the font to Courier. It might be better to
arrange your list the other way round, i.e.:

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

This way you will only need to add zeroes to those numbers which are
only 5 digits or less, and it doesn't matter how long the text is. The
formula to achieve this would be:

=TEXT(B1,"000000")&" - "&A1

Hope this helps.

Pete
 
B

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.
 
Top