+ Reply to Thread
Results 1 to 9 of 9

How to generate a LIST from codes

  1. #1
    Registered User
    Join Date
    01-05-2009
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    41

    How to generate a LIST from codes

    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
    Last edited by bitswit; 08-21-2010 at 05:26 AM.

  2. #2
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to generate a LIST from codes

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


    If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.

  3. #3
    Registered User
    Join Date
    01-05-2009
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: How to generate a LIST from codes

    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

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to generate a LIST from codes

    try this see shet 2 for table and lookups.
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    01-05-2009
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: How to generate a LIST from codes

    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

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to generate a LIST from codes

    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

  7. #7
    Registered User
    Join Date
    01-05-2009
    Location
    New Delhi
    MS-Off Ver
    Excel 2007
    Posts
    41

    Re: How to generate a LIST from codes

    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 !!

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to generate a LIST from codes

    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))

  9. #9
    Forum Expert
    Join Date
    08-27-2008
    Location
    England
    MS-Off Ver
    2010
    Posts
    2,561

    Re: How to generate a LIST from codes

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1