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)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
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

Eliminating blank cells in a list on a ROW
 

How odd! The second question today about this formula

=IF(COLUMN()-COLUMN(NoBlanksRange)+1COLUMNS(BlanksRange)-COUNTBLANK(BlanksR
ange),"",
INDIRECT(ADDRESS(ROW(BlanksRange),SMALL((IF(Blanks Range<"",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" wrote in
message ...

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)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
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

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 ,
grime 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)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Thanks in advance!

Eliminating blank cells in a list on a ROW
 

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

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" wrote in
message ...

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

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 ,
grime 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.