+ Reply to Thread
Results 1 to 9 of 9

List has blanks in it, create new column without the blanks

  1. #1
    Forum Contributor
    Join Date
    12-22-2006
    Posts
    114

    List has blanks in it, create new column without the blanks

    I have a list that imports into excel and it has many blank rows in between the fields, I dont want to touch this data, but on a new sheet I want to have a list of all the values but with no spaces. Therefore everytime I import new data it will automatically give a list in the other sheet without all the blank spaces. Is there a formula where it will show a figure, and if it is a blank cell it will scan down to the next empty cell and then display the value in that cell?

    Thanks in advance,

    Alex

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

    Re: List has blanks in it, create new column without the blanks

    so i havent got to re invent the wheel lol
    its nicely done here
    http://www.cpearson.com/excel/noblanks.htm
    "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

  3. #3
    Forum Contributor
    Join Date
    12-22-2006
    Posts
    114

    Re: List has blanks in it, create new column without the blanks

    Thats great, thanks very much. I was wondering though, is it possible to alter this so that say for example as the data comes in in column D in sheet 1, I want the list with no blanks to be in column A in sheet 2. I cant see where in the formula I can change the look up location.

    Thanks,

    Alex

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

    Re: List has blanks in it, create new column without the blanks

    if you create those named ranges it doesnt matter what sheet they are on
    so say blanksrange refers to sheet1!d1:d1000 and noblanksrange refers to sheet2!a1:a1000
    just put that formula in sheet2 a1 cofirm with CSE and drag down

  5. #5
    Forum Contributor
    Join Date
    12-22-2006
    Posts
    114

    Re: List has blanks in it, create new column without the blanks

    I have tried the original formula in a trial sheet which worked and then i tried the cross reference between sheets and it isnt working
    Attached Files Attached Files

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

    Re: List has blanks in it, create new column without the blanks

    i see why ,it is trying to ref the same sheet it needs to be adjusted to add sheet name where blanks range is
    =IF(ROW()-ROW(NOBLANKS)+1>ROWS(BLANKS)-
    COUNTBLANK(BLANKS),"",INDIRECT("SHEET1!"&ADDRESS(SMALL(
    (IF(BLANKS<>"",ROW(BLANKS),ROW()+ROWS(BLANKS))),
    ROW()-ROW(NOBLANKS)+1),COLUMN(BLANKS),4)))
    Attached Files Attached Files
    Last edited by martindwilson; 06-25-2009 at 09:53 AM.

  7. #7
    Forum Contributor
    Join Date
    12-22-2006
    Posts
    114

    Re: List has blanks in it, create new column without the blanks

    Thanks very much! That is fantastic

    Alex

  8. #8
    Forum Contributor
    Join Date
    12-22-2006
    Posts
    114

    Re: List has blanks in it, create new column without the blanks

    What if the cells arent blank and they actually have a value of 0 is there anyway that these 0's can be included to be eliminated in the same formula?

  9. #9
    Registered User
    Join Date
    11-03-2010
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2007
    Posts
    14

    Question Re: List has blanks in it, create new column without the blanks

    I have an extended question on this.

    I have used the formula in the example and for one column it works great.

    But I wonder if it is possible to have BlanksRange to cover more than one column?

    I cannot add attachments for some reason, but instead of column A going into B without blanks I would like column C and D to go into E without blanks.

    /Viktor

+ 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