seperate values from a column of values

blueflinko

New Member
Joined
Feb 17, 2004
Messages
11
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

wadere

New Member
Joined
Sep 21, 2006
Messages
4
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
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi blueflinko

In C1:

Code:
=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
ABCD
101
203
304
419
50
60
73
84
90
100
119
12
Sheet2
 

Forum statistics

Threads
1,136,878
Messages
5,678,307
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top