+ Reply to Thread
Results 1 to 4 of 4

Concatenate combination with text, carriage return, and IF formula to ignore blank cells

  1. #1
    Registered User
    Join Date
    11-20-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2012
    Posts
    11

    Concatenate combination with text, carriage return, and IF formula to ignore blank cells

    Good-morning, I hope you're having a fabulous day. My mind has turned to mush trying to think this through and I need some help from those of you with more brainpower and experience than I have. I haven't found a concatenate formula solution for this, so your kind assistance would be very appreciated!!!

    I have attached an example file which shows multiple client names in Column A, along with a header row of generic Client Kits (CK) numbered 1-19 in this example, but in reality sometimes going to something as high as CK45.

    Cells V9 and V10 show the result I would want to see from the concatenate function. It would:

    --- Result in a consecutive "1) ", "2) ", "3) "... number for each of the result components (the latter will be in varying cells across that row)
    --- Ignore any blank cell where no Client Kit identifier is showing
    --- Have a carriage return (I presume CHAR(10) would accomplish that) after each individual numbered item in the aforementioned 1,2,3,4... etc. sequence
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Concatenate combination with text, carriage return, and IF formula to ignore blank cel

    It will be quite difficult the desired output as you showed in the sample file with the help of a formula.
    To get the exact output, you will need a VBA code to achieve this. If you are open to a VBA solution, please find the attached sheet and click on the Green Button to get the desired output in col. V. See if this is something you can work with.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    11-20-2013
    Location
    California, USA
    MS-Off Ver
    Excel 2012
    Posts
    11

    Re: Concatenate combination with text, carriage return, and IF formula to ignore blank cel

    Your solution is a work of art and I can appreciate how perfect the results are, so a huge Thank You for that. Unfortunately I am not -- yet -- a VBA person, but it appears I need to go focus on getting some additional skills, quickly. Thank you so much, amazing--and the speed with which you developed it. Wow.

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: Concatenate combination with text, carriage return, and IF formula to ignore blank cel

    In that case you should wait and see if someone has a solution to get the desired output with the help of a formula. Sorry that I couldn't help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Concatenate cells with 5 characters each column but ignore if blank
    By ghiegomez in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-25-2020, 12:09 AM
  2. Concatenate, Skip Blanks and Carriage Return only after non-blank
    By GTide in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2014, 05:08 PM
  3. [SOLVED] Return non-blank cells from range AND ignore cells with only formula w/o results
    By grafitti in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-06-2014, 08:15 AM
  4. Replies: 4
    Last Post: 11-20-2013, 10:16 PM
  5. Replies: 1
    Last Post: 06-29-2012, 01:16 PM

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