output 1st, 2nd, 3rd ... non-zero value in a column

benbulloch

New Member
Joined
Dec 14, 2010
Messages
9
I am having difficulty outputting only the non-zero values (computed from a formula) from a column buried towards the bottom of a sheet. I can make an array formula work if the values are in f1:f100 on a clean sheet using this ctrl-shift-enter formula entered in I9: {=INDEX($F$1:$F$100,SMALL(IF($F$2:$F$100>0,ROW($F$2:$F$100)),ROW(A1)),1)}

I can drag this formula down to output the subsequent non-zero figures in my range. The drag (obviously) changes the ROW(A1) portion of the formula to ROW(A2)...

When my range is actually in a very cluttered template (not built by me!) at D127:D184 with the formula in F136:F146

=INDEX(D127:D184,SMALL(IF(D128:D184>0,ROW(D128:D184)),ROW(F128)),1)

I get a #num error. Driving me crazy... I suspect a problem with my last ROW argument but nothing seems to work.

Data at D127:D145 (truncated)
$0
$0
$0
$0
$0
$0
$0
$0
$0
$0
$311,844
$0
$0
$0
$0
$428,786
$0
$0
$2,097,332


Should return at F136:F138
$311,844
$428,786
$2,097,332

It would also be great to have a companion formula in G136:G138 that outputs the value from column B (a date in my model) when column D is non-zero. Is this another array formula? Vlookup?


Thanks so much! I look forward to learning from you all!
 
Respected Mr. Aladin Akyurek,
Thanks for your quick solution. As for numbers, It works like magic.

For Text, as reproduced below,

To list only text values from A2:N2...

A5, control+shift+enter, not just enter, and copy across:
Code:
=IF(COLUMNS($A$5:A5)<=$A$4,INDEX($A$2:$N$2,
SMALL(IF(ISTEXT($A$2:$N$2),COLUMN($A$2:$N$2)-COLUMN($A$2)+1)),
COLUMNS($A$5:A5))),"")
 
this code gives an error. there is an extra bracket ")"after COLUMN($A$2)+1). On removing it, the code will be
 
=IF(COLUMNS($A$5:A5)<=$A$4,INDEX($A$2:$N$2,SMALL(IF(ISTEXT($A$2:$N$2),COLUMN($A$2:$N$2)-COLUMN($A$2)+1),COLUMNS($A$5:A5))),"")
 
This works very fine.
Thanks a lot, once again.
Satish[/QUOTE]
 
You are welcome. Thanks for the catch. And I appreciate the feedback.
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
A B C
1 xyz xyz xyz
2 0 rst rst
3 0
4 rst
5
6 ABC
7 DEF

I have text data in column A1:A4. After getting rid of two zero entries using the array formula, I get the non zero texts list in column B consisting of two entries from A1:A4. In column C, I want these two entries and I also want to have the contents of A6 and A7 to appear in C5 and C6 automatically. How do I get it?

Thanks a lot,
Satish
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,694
Messages
6,192,473
Members
453,726
Latest member
JoeH57

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