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

[benbulloch]

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!

 

[Aladin Akyurek]

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:

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

 

[sfshah]

Thanks, Mr. T Valco,

You have made is more simple. It works very well.
Satish.

 

[sfshah]

Thanks, Biff,

You have made it more simple. It works very well.
Satish

 

[T. Valko]

Thanks, Biff,

You have made it more simple. It works very well.
Satish

You're welcome. Thanks for the feedback!

 

[sfshah]

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