Closed Thread
Results 1 to 4 of 4

Aggregate data / remove empty columns

  1. #1
    Registered User
    Join Date
    04-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Aggregate data / remove empty columns

    I have a worksheet in which rows have empty column values, e.g.

    Name,Column1,Column2,Column3
    Bob,Apples,<blank>,Peaches
    Joan,<blank>,Oranges,<blank>
    Ted,Apples,Oranges,Peaches

    Note: The commas above indicate separate columns.

    I'd like a formula to evaluate these results and strip out the blanks. Results should look like this:


    Bob,Apples,Peaches
    Joan,Oranges
    Ted,Apples,Oranges,Peaches.

    I've seen some examples on this forum ... but they never seem to be laid out the exact way my data is organized!

    Thanks for any help out there!

  2. #2
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Aggregate data / remove empty columns

    1. Select the whole range of data.
    2. Press Ctrl+G
    3. Press "Special" button
    4. Select "Blanks" and press OK
    5. Press Ctrl+"-" (Ctrl + minus)
    6. Select 1st option that is "Shift cells left" and press OK


    OR

    1. Select the whole range of data.
    2. On Home Tab in Editing group click on the small arrow below Find & Select.
    3. Select Go to Special...
    4. Select "Blanks" and press OK
    5. Press Ctrl+"-" (Ctrl + minus)
    6. Select 1st option that is "Shift cells left" and press OK
    Last edited by Faraz.Ahmed; 04-16-2014 at 06:06 PM.

  3. #3
    Registered User
    Join Date
    04-16-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Aggregate data / remove empty columns

    Thank you for the reply. I'm sorry that I wasn't more clear. The original dataset is variable - it will change over time, so I want to use formulas to produce the "empty column" view as the source dataset changes. (I don't want to use a manual "remove column" action each time the dataset changes.)

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Aggregate data / remove empty columns

    Closed due to OP request
    Audere est facere

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Remove whitespace from empty (non-existent) columns
    By throwaway992 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-20-2013, 12:17 AM
  2. Aggregate data in a range of columns to remove blank cells
    By arbgd1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2013, 04:11 PM
  3. [SOLVED] Remove Empty Rows ,Columns & Sort
    By johncena in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-18-2013, 03:40 AM
  4. remove empty spaces in data
    By kisboros in forum Excel General
    Replies: 8
    Last Post: 02-13-2013, 12:06 AM
  5. Help remove the empty columns or rows
    By SimonLee in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-20-2011, 11:20 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