Non-Array Play to Eliminate Blank Cells
 

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.

Non-Array Play to Eliminate Blank Cells
 

http://www.cpearson.com/excel/noblanks.htm
--
Gary''s Student - gsnu200801


"Peter" wrote:

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.

Non-Array Play to Eliminate Blank Cells
 

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,SM ALL(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)

--
Biff
Microsoft Excel MVP


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

Non-Array Play to Eliminate Blank Cells
 

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


"Peter" wrote:

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.

Non-Array Play to Eliminate Blank Cells
 

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,R OWS($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" wrote:
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.

Non-Array Play to Eliminate Blank Cells
 

Your solution worked. Thanks a ton!

"Gary''s Student" wrote:

http://www.cpearson.com/excel/noblanks.htm
--
Gary''s Student - gsnu200801


"Peter" wrote:

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.