+ Reply to Thread
Results 1 to 6 of 6

Eliminating blank cells in a list on a ROW

  1. #1
    Registered User
    Join Date
    02-01-2005
    Posts
    62

    Question Eliminating blank cells in a list on a ROW

    I need help converting Pearson's array formula from a list in a column to a list in a row:

    http://www.cpearson.com/excel/noblanks.htm

    =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
    COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
    (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
    ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

    Thanks in advance!

  2. #2
    Bob Phillips
    Guest

    Re: Eliminating blank cells in a list on a ROW

    How odd! The second question today about this formula

    =IF(COLUMN()-COLUMN(NoBlanksRange)+1>COLUMNS(BlanksRange)-COUNTBLANK(BlanksR
    ange),"",
    INDIRECT(ADDRESS(ROW(BlanksRange),SMALL((IF(BlanksRange<>"",COLUMN(BlanksRan
    ge),COLUMN()+COLUMNS(BlanksRange))),COLUMN()-COLUMN(NoBlanksRange)+1),ROW(Bl
    anksRanges),4)))

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grime" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I need help converting Pearson's array formula from a list in a column
    > to a list in a row:
    >
    > http://www.cpearson.com/excel/noblanks.htm
    >
    > =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
    > COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
    > (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
    > ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
    >
    > Thanks in advance!
    >
    >
    > --
    > grime
    > ------------------------------------------------------------------------
    > grime's Profile:

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




  3. #3
    Domenic
    Guest

    Re: Eliminating blank cells in a list on a ROW

    Try...

    B1, copied across:

    =IF(COLUMNS($B1:B1)<=ROWS($A$1:$A$7)-COUNTBLANK($A$1:$A$7),INDEX($A$1:$A$
    7,SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),COLUMNS($B1:B1))),""
    )

    ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

    Hope this helps!

    In article <[email protected]>,
    grime <[email protected]> wrote:

    > I need help converting Pearson's array formula from a list in a column
    > to a list in a row:
    >
    > http://www.cpearson.com/excel/noblanks.htm
    >
    > =IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
    > COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
    > (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
    > ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
    >
    > Thanks in advance!


  4. #4
    Registered User
    Join Date
    02-01-2005
    Posts
    62
    Bob and Dom,

    Thanks for the reply, but neither formula did the trick. I entered both in as array formulas, and Bob, I even changed your BlanksRanges to BlanksRange.

    To summarize again, I have data in a range A1:M1 that contains blanks and I want the list from row 1 into row 2 (A2:M2) without blanks.

    Thanks again.

  5. #5
    Bob Phillips
    Guest

    Re: Eliminating blank cells in a list on a ROW

    In my test I had to use different names as I had already used the other two
    in the previous question, and did correct them all in the posting, but that
    apart, I tested it and it did work. Did you commit it with Ctrl-Shift-Enter?

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "grime" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Bob and Dom,
    >
    > Thanks for the reply, but neither formula did the trick. I entered
    > both in as array formulas, and Bob, I even changed your BlanksRanges to
    > BlanksRange.
    >
    > To summarize again, I have data in a range A1:M1 that contains blanks
    > and I want the list from row 1 into row 2 (A2:M2) without blanks.
    >
    > Thanks again.
    >
    >
    > --
    > grime
    > ------------------------------------------------------------------------
    > grime's Profile:

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




  6. #6
    Domenic
    Guest

    Re: Eliminating blank cells in a list on a ROW

    Try the following instead...

    A2, copied across:

    =IF(COLUMNS($A2:A2)<=COLUMNS($A$1:$M$1)-COUNTBLANK($A$1:$M$1),INDEX($A$1:
    $M$1,SMALL(IF($A$1:$M$1<>"",COLUMN($A$1:$M$1)-COLUMN($A$1)+1),COLUMNS($A2
    :A2))),"")

    ....confirmed with CONTROL+SHIFT+ENTER.

    Hope this helps!

    In article <[email protected]>,
    grime <[email protected]> wrote:

    > Bob and Dom,
    >
    > Thanks for the reply, but neither formula did the trick. I entered
    > both in as array formulas, and Bob, I even changed your BlanksRanges to
    > BlanksRange.
    >
    > To summarize again, I have data in a range A1:M1 that contains blanks
    > and I want the list from row 1 into row 2 (A2:M2) without blanks.
    >
    > Thanks again.


+ 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