removing blanks

[freekrill]

Hi,

I have a column of data that includes values and blanks

eg.

a1 5
a2 ""
a3 11
a4 9
a5 ""
a6 4
a7 ""
a8 5

What I want to be able to do is to transfer this data to column b
keeping the data in the same order, but eliminating the blanks.

ie.

b1 5
b2 11
b3 9
b4 4
b5 5

I know how to do this using code, but is there a way of doing it usin
formulae?

Thanks
fre

 

[Nick Hodge]

Freekrill

Set up a column C and type 1 in cell C1 and 2 in cell c2 and copy down as
far as the data goes in A. This should give you an 'index' in C.

Sort by column A and the blanks will all go to the top or bottom, depending
on your sort order. Copy the 'data' to column B or delete the blanks, if
that is your need and then resort by you index column C.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Biff]

Or, if you want a formula solution:

Entered as an array CTRL+SHIFT+ENTER:

=INDEX(A1:A8,SMALL(IF(A1:A8<>"",ROW(A1:A8)),ROW(1:1)))

Biff

 

[Biff]

-----Original Message-----
Or, if you want a formula solution:

Entered as an array CTRL+SHIFT+ENTER:

=INDEX(A1:A8,SMALL(IF(A1:A8<>"",ROW(A1:A8)),ROW(1:1)))

Biff

P.S. - copy down until you get #NUM! error.