+ Reply to Thread
Results 1 to 4 of 4

CONCATENATE data in row but deleting blank cells

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    Minnesota, US
    MS-Off Ver
    Excel 2010
    Posts
    2

    CONCATENATE data in row but deleting blank cells

    I need some help in transferring data. I have data that is input on worksheet, pulled into another worksheet by sorting it meeting certain parameters, using IF functions. However, I need to concatenate it to a third worksheet into groups, deleting the blank cells. I have used the CONCATENATE, but need to eliminate the blank rows, otherwise the cell I am transferring the data to is huge. Here is an example:



    Apple

    Banana
    Grapes



    Kiwi

    Pear



    However, this data might change month to month. I want to combine this information into one cell to look like, while eliminating blank cells that might appear before, after or inbetween the items in the listing:

    Apple
    Banana
    Grapes
    Kiwi
    Pear


    I have no programming skills and would be at a loss using VB.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: CONCATENATE data in row but deleting blank cells

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-15-2013
    Location
    Minnesota, US
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: CONCATENATE data in row but deleting blank cells

    Attached is the sample file I have been working on with obvious fake data. I want to pull data from the 'data' worksheet to sort appropriately into the correct risk box on the 'riskchart' worksheet, based on the risk score in the 'data' worksheet.
    Attached Files Attached Files

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: CONCATENATE data in row but deleting blank cells

    If you referring to cell H34 where your concatenate formulas is, there are no extra spaces. Typically, when you do concatenate cells there will no spaces at all even when you have empty cells. If you click on H34 and remove Wrap Text, you will see that there are no extra spaces in the cell.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. deleting blank rows in a concatenate formula
    By jettakay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-20-2013, 04:56 PM
  2. [SOLVED] Concatenate non-blank cells
    By tone640 in forum Excel General
    Replies: 4
    Last Post: 05-16-2012, 05:31 AM
  3. Using concatenate and deleting source cells
    By htown in forum Excel General
    Replies: 2
    Last Post: 06-06-2011, 02:39 PM
  4. Replies: 0
    Last Post: 04-07-2009, 04:42 PM
  5. [SOLVED] Concatenate Non Blank Cells
    By ssjody in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-31-2005, 08:30 AM

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