lsit value and skip blanks

R

Ron

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
 
M

Max

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
 
A

Aladin Akyurek

Ron said:
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
Click to expand...

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.
 
Top