How to generate a LIST from codes

**bitswit** · 08-20-2010, 04:24 AM

Hi, I work on Excel 2003 and XP-SP2 operating system. I have approx 26 error types (say from A-Z), like :
Code------- Description
A ----------- No address
B ----------- No account
C ----------- No name, etc etc.

I would like to input the error code in a cell, and get the description in the adjoining cell, one beneath the other. E.g. if I type only C in cell A1, then the output in cell B1 should be No Name.

However, if I type ABC in cell A2, then output in cell B2 should look like :
No address
No account
No name

I checked lots of posts but could not find the answer, so if anyone can help me with this, I will be very grateful. thanks in advance....b

**Cheeky Charlie** · 08-20-2010, 05:34 AM

What's the maximum number of codes in any one cell?

**bitswit** · 08-20-2010, 05:57 AM

Well, like I said in my original post, the upper limit of my error codes is 26. So, ideally I should be able to say, write any combo of codes e.g. ACDFGM and the descriptions for these codes should output into the next cell. However, I want each description coming in a seperate line, within the same cell. E.g.if I have typed ACDFGM into cell A1, then my output cell B1 could look like :

No Address
No PIN
No ZIP Code
No Cash
No Assets
No Car

**martindwilson** · 08-20-2010, 06:01 AM

try this see shet 2 for table and lookups.

**bitswit** · 08-21-2010, 02:28 AM

Hi Martin, appreciate your quick reply. Just a few points remain :
1. If i input say 3-4 error codes into cell A2, the output comes out fine in cell B2. However, if i input say ABCDEF into cell A2, then cell B2 only shows first four descriptions. Problem is that the last two descriptions are visible only if the row size is increased. Any way to get around this, that the row is re-sized according to the width of data input into cell B2?

2. If i place cursor on cell B2, I want to be able to only see the output and not the formula. How to do it?

3. I do not want any person to view/ modify either the formula cell in column B, or the entire sheet2 where my error codes and descriptions will be stored. How to work this out?

Once again, thanks for your support and look forward to your solution. Best regards, B

**martindwilson** · 08-21-2010, 03:44 AM

the cells wont auto resize with out some vba code, if you go down that path you might as well have the whole thing done that way, it would probably be much better.
now you could just wait and hope someone posts or start a new entry in the programming forum called something like "convert vlookup to vba and auto resize cells to fit results"
linking to this thread

**bitswit** · 08-21-2010, 05:14 AM

Thanks for all your assistance Martin.. I think my work can easily be done with the solution you have provided and we can get around the row size issue by selecting the rows and double-clicking to resize.... thankx once again !!

**martindwilson** · 08-21-2010, 06:19 AM

you might want to try replacing formula in b2 with that will ensure you get carriage returns in the right place

=IF(A2="","",CHOOSE(LEN(A2),Sheet2!A1,Sheet2!A1&CHAR(10)&Sheet2!B1,Sheet2!A1&CHAR(10)&Sheet2!B1&CHAR(10)&Sheet2!C1,Sheet2!A1&CHAR(10)&Sheet2!B1&CHAR(10)&Sheet2!C1&CHAR(10)&Sheet2!D1,Sheet2!A1&CHAR(10)&Sheet2!B1&CHAR(10)&Sheet2!C1&CHAR(10)&Sheet2!D1&CHAR(10)&Sheet2!E1,Sheet2!A1&CHAR(10)&Sheet2!B1&CHAR(10)&Sheet2!C1&CHAR(10)&Sheet2!D1&CHAR(10)&Sheet2!E1&CHAR(10)&Sheet2!F1,Sheet2!A1&CHAR(10)&Sheet2!B1&CHAR(10)&Sheet2!C1&CHAR(10)&Sheet2!D1&CHAR(10)&Sheet2!E1&CHAR(10)&Sheet2!F1&CHAR(10)&Sheet2!G1))

**Cheeky Charlie** · 08-22-2010, 05:33 PM

I'm not sure you understood my question - the maximum number of error codes in any one cell, i.e. in a single cell in column A, the total number of letters which might be included.

If it helps to clarify, mdw's solution is good to 7, try putting 8 letters in to a cell in col A on sheet 1.

Depending on the maximum you might need, different solutions (I agree with mdw, probably VBA if it gets much more complicated) are appropriate.

hth