+ Reply to Thread
Results 1 to 5 of 5

Removing Blank Rows

  1. #1
    Registered User
    Join Date
    02-04-2006
    Posts
    11

    Removing Blank Rows

    I have an excel workbook with three sheets:

    First Sheet

    This sheet has three columns. Column A is simply a set of random numbers. Column B is empty and is designed so that the spreadsheet user can place an x next to values in Column A that the user would like to "mark". Column C has an "if" formula that basically says, =if(B1="x", A1, "")

    So now, any "A" cell that I select by placing an "x" in the "B" column is copied into Column C.


    Second Sheet

    I'd like Column A in this sheet to pull the values from Column C in the previous sheet, but without skipping any lines. In other words, I want the values from Sheet1:ColumnC, but collapsed so that they're all next to each other--nice and neat.


    Is this possible?

  2. #2
    Max
    Guest

    Re: Removing Blank Rows

    One way, via non-array formulas ..

    In Sheet1, slightly change the formula in col C
    Put in C1: =IF(B1="x",ROW(),"")
    Copy C1 down to last row of data in col A

    Then in Sheet2, we could put in A1:
    =IF(ISERROR(SMALL(Sheet1!C:C,ROW(A1))),"",
    INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!C:C,ROW(A1)),Sheet1!C:C,0)))
    Copy A1 down to cover the extent of col A in Sheet1

    Col A will return the desired results from Sheet1's col A (i.e. numbers
    marked with an "x" in Sheet1's col B), all neatly bunched at the top
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "inveni0" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I have an excel workbook with three sheets:
    >
    > _FIRST_SHEET_
    >
    > This sheet has three columns. Column A is simply a set of random
    > numbers. Column B is empty and is designed so that the spreadsheet
    > user can place an x next to values in Column A that the user would like
    > to "mark". Column C has an "if" formula that basically says,
    > =if(B1="x", A1, "")
    >
    > So now, any "A" cell that I select by placing an "x" in the "B" column
    > is copied into Column C.
    >
    >
    > _SECOND_SHEET_
    >
    > I'd like Column A in this sheet to pull the values from Column C in the
    > previous sheet, but without skipping any lines. In other words, I want
    > the values from Sheet1:ColumnC, but collapsed so that they're all next
    > to each other--nice and neat.
    >
    >
    > Is this possible?
    >
    >
    > --
    > inveni0
    > ------------------------------------------------------------------------
    > inveni0's Profile:

    http://www.excelforum.com/member.php...o&userid=31178
    > View this thread: http://www.excelforum.com/showthread...hreadid=508361
    >




  3. #3
    Herbert Seidenberg
    Guest

    Re: Removing Blank Rows

    Another way, via Advanced Filter

    ListA ListB ListC
    A
    B x B
    C
    D
    E x E
    F
    G x G
    H
    I
    J
    K x K
    L x L

    Criteria
    Test
    #VALUE!

    ListA
    B
    E
    G
    K
    L

    Select A1:A13 and name it Database.
    This includes ListA and A thru L.
    Use Insert > Name > Define
    Again select A1:A13 and
    Insert > Name > Create > Top Row
    Select the 3 cells beginning with Criteria and
    Insert > Name > Create > Top Row
    In the cell that says #VALUE! enter this formula:
    =OFFSET(ListA,,1)="x"
    Data > Filter > Advanced Filter
    Check: Copy to another location
    The List and Criteria fields were filled automatically.
    Copy To: enter a location on sheet2


  4. #4
    Registered User
    Join Date
    02-04-2006
    Posts
    11
    Thanks for the advice, guys. I can't try it now (I'm working on another project) but I'll give it a shot tonight and post the results. I appreciate the response!

  5. #5
    Max
    Guest

    Re: Removing Blank Rows

    You're welcome. Let us know how it went for you.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "inveni0" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the advice, guys. I can't try it now (I'm working on another
    > project) but I'll give it a shot tonight and post the results. I
    > appreciate the response!




+ 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