Non-Array Play to Eliminate Blank Cells

[Peter]

Column A shows:

1.Apple
2.{Space}
3.Banana
4.{Space}
5.Yellow
6.Red

How do I make Column B show:

1.Apple
2.Banana
3.Yellow
4.Red

I've seen some hints at how to do this with numbers, but can you figure out
a solution with text?

Hint?:

A simple non-array play ..
In B1: =IF(A1="","",ROW())
In C1: =IF(ROW()>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW())))
Copy B1:C1 down to cover the max expected extent of data in col A.
Minimize/hide away col B. Col C will return the results that you seek, all
neatly bunched at the top.

 

[Gary''s Student]

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

 

[T. Valko]

If you don't want to clutter up your file with helper cells try this array
formula** :

Assuming {Space} means it's an EMPTY cell.

=IF(ROWS(B$1:B1)<=COUNTA(A$1:A$6),INDEX(A$1:A$6,SMALL(IF(A$1:A$6<>"",ROW(A$1:A$6)),ROWS(B$1:B1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Teethless mama]

Highlight your data > hit F5 > Special > Blanks > OK > Edit > Delete > Shift
Cells Up

 

[Max]

The simple, fast & easy-to-understand non-array method
that you posted works for both numbers and text in the source col A.

Should your source data in col A start from any row other than row1*, just a
slight adjustment is needed to col C's formula, viz replace ROW() with
ROWS($1:1). *Believe this might be what is tripping things up for you over
there

Eg assume col A's data starts in row3 down

You could use:
In B3: =IF(A3="","",ROW())
Ensure cells above, ie B1:B2 are left blank

In C3: =IF(ROWS($1:1)>COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROWS($1:1))))
Copy B3:C3 down to cover the max expected extent of data in col A.
Minimize/hide away col B. Col C will return the results that you seek, all
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Peter]

Your solution worked. Thanks a ton!

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