+ Reply to Thread
Results 1 to 11 of 11

Combining data from multiple cells while skipping blanks

  1. #1
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Combining data from multiple cells while skipping blanks

    Hello, I am trying to create a template for my staff however I am running into an issue and I don't know if I can accomplish what I am hoping for. I currently have empty cells in b2:b16 where my staff can enter medical codes. In cells c2:c16 I have a lookup formula that pulls the definition of the code from a seperate work book. What I am trying to accomplish is in cell a95 toward the bottom of the work book i want to combine all of the descriptions from cells c2:c16 seperated by a ;. The issue I'm running into is that unless all of these fields are filled out I get an error. When they are completely filled out it does exactly what I want. Unfortunatly I cannot predict whether one request will come in with 1 code or 15 codes so it varies. Is there a way to combine cells as needed? Example shown below:

    This code works when I have all fields completed:
    =A2&""&C2&"; "&C3&"; "&C4&"; "&C5&"; "&C6&"; "&C7&"; "&C8&"; "&C9&"; "&C10&"; "&C11&"; "&C12&"; "&C13&"; "&C14&"; "&C15&"; "&C16

    A2 is the word "Request" btw

    This is what it returns:
    Request: 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL

    Request: But if I am missing ,for example, 14 of these fields it returns this:
    #N/A

    And I want it to return this:
    Request: 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL; 72148 MRI SPINAL CANAL LUMBAR W/O CONTRAST MATERIAL

    Any help would be greatly appreciated, I have been playing with this for a while.
    Attached Files Attached Files
    Last edited by htek9; 07-13-2012 at 05:45 PM.

  2. #2
    Valued Forum Contributor tlafferty's Avatar
    Join Date
    04-08-2011
    Location
    United States, Tacoma, WA
    MS-Off Ver
    Excel 2010, Excel 2013 Customer Preview
    Posts
    1,112

    Re: Combining data from multiple cells while skipping blanks

    As a first step, I'm going to guess that your lookup formulas are in the cells C2, C3 etc. Add this to your lookup formulas: IFERROR(YOURLOOKUP,""). You will still get Request: 72148;; kinds of results where there's a blank because your concatenation formula will still put in a ; for each part of the formula.
    If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
    Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...

    Thomas Lafferty
    Analyst/Programmer

  3. #3
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Combining data from multiple cells while skipping blanks

    This might help
    http://chandoo.org/wp/2008/05/28/how...-excel-concat/
    //Ola

  4. #4
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining data from multiple cells while skipping blanks

    Thank you for your reply, I still cannot seem to get it to work. I have attached a sample of the file that I am working with so you can see exactly what I am trying to do. Currently the file works fine if you fill in all fields, however if you only fill in a couple of them with codes from the CPT sheet it will not work.

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Combining data from multiple cells while skipping blanks

    This concatenation seemed to work:
    Formula: =ConcatIf(C2:C16;E2:E16;FALSE;", ")
    Column E: =OR(ISERROR(C2);ISBLANK(C2))
    //Ola

    =Concatif(Concat Range, Validation Range, Validation, [Seperator])
    Code: http://chandoo.org/wp/2008/05/28/how...comment-201373
    Description: http://chandoo.org/wp/2008/05/28/how...comment-196352

  6. #6
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining data from multiple cells while skipping blanks

    Where would I put the formula? And are you saying I need to create a seperate column E to accomplish what I want?

  7. #7
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Combining data from multiple cells while skipping blanks

    Here is another example.
    //Ola


    A95: =A2&""&StringConcat("; ";IF(ISTEXT(C2:C16);C2:C16;""))
    The formula has to be confirmed by Ctrl+Shift+Enter, not just Enter.

    Your instruction: "What I am trying to accomplish is in cell a95 ... i want to combine all of the descriptions from cells c2:c16 seperated by a ;"
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining data from multiple cells while skipping blanks

    This worked perfectly! Thank you so much. One more question if its possible. I attahced an updated version of the spreadsheet and I used your method to also pull dx codes. The only issue I am running into is when I type the dx code in in b21 I can format it to stay in a "000.00" format. In c21 it looks for the code on the dx sheet and returns the code as well as the description. My question is, can I do something to keep the "000.00" format on either the returned cell c21 followed by the description? If not is there a way to format the formula in a44 to display for example 002.1 and description instead of 2.1 and the description?
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    Re: Combining data from multiple cells while skipping blanks

    Perhaps this, or something similar?
    //Ola

    C21: =TEXT(B21;"000.00")&" "&VLOOKUP(B21;DX!A:B;2;FALSE)

  10. #10
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining data from multiple cells while skipping blanks

    I tried that formula but for some reason it gives me an error message at B21

  11. #11
    Registered User
    Join Date
    07-12-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Combining data from multiple cells while skipping blanks

    I figured it out, I had to change the ";" to "," in the formula and it worked perfect. Thank you all so much for your help this website is amazing!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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