I have a list in a column but some values are blank. I would like to make a
new list in another column but without the blanks.

thx
Ron

One way, assuming you want it dynamic ..

Assume source list is in col A

Put in C1: =IF(TRIM(A1)="","",ROW())

Put in B1:
=IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(C:C,ROWS($A$1
:A1)),C:C,0)))

Select B1:C1, fill down to say, C200,
to cover the max expected data range in col A

Col B will return the desired results from col A
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Ron" wrote in message
...
I have a list in a column but some values are blank. I would like to make
a
new list in another column but without the blanks.

thx
Ron

Ron wrote:
I have a list in a column but some values are blank. I would like to make a
new list in another column but without the blanks.

thx
Ron

Let A2:A100 house the list of interest with a label in A2.

In B1 enter: 0

In B3 enter & copy down:

=IF(A3<"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1 ,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,LOOKUP(ROW()-ROW(C$3)+1,B:B,A:A),"")

The range in C will house a new list with no interspersed blanks.