seperate values from a column of values

[blueflinko]

I have a column of numbers for which most of the numbers are 0 (zero). However there are 4 numbers in this column which are not zero. I would like to display those 4 numbers outside of that column without any zeros.

For example my column looks like:

0
0
0
1
0
0
3
4
0
0
9

I want to return in a different column only the numbers other than zero:

1
3
4
9


I am running Excel 2000 on Windows XP.

 

[wadere]

Greetings!

Give this a try

sub noZeros()
i=0
for each xCell in range("yourrangehere")
if not xCell.value=0 then
Range("otherrange").offset(i,0) = xCell
i=i+1
endif
next xCell

HOpe this helps

 

[scu77]

deleted

 

[pgc01]

Hi blueflinko

In C1:

=INDEX($A$1:$A$11,SMALL(IF($A$1:$A$11<>0,ROW($A$1:$A$11)),ROW()-ROW($C$1)+1))

This is an array formula and so MUST be entered with CTRL+SHIFT+ENTER and not just ENTER.
Copy down.

Hope this helps
PGC

EDIT: Replaced B1 by C1

Book1
	A	B	C	D
1	0		1
2	0		3
3	0		4
4	1		9
5	0
6	0
7	3
8	4
9	0
10	0
11	9
12
Sheet2