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!

 

[AdamL]

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.

Try:

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

or perhaps more robust:

=INDEX(D$127:D$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$128)+1),ROWS(A$1:A1)),1)

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?

=INDEX(B$127:B$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$128)+1),ROWS(A$1:A1)),1)

HTH
Adam

 

[benbulloch]

Most excellent Adam! I knew I was getting close. I had to tweak very slightly to:

{=INDEX(D$127:D$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$128)+2),ROWS(A$1:A1)),1)}

and
{=INDEX(B$127:B$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$127)+1),ROWS(A$1:A1)),1}

Works like a dream. Thank you for your help.
Ben Bulloch

 

[Aladin Akyurek]

Most excellent Adam! I knew I was getting close. I had to tweak very slightly to:

{=INDEX(D$127:D$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$128)+2),ROWS(A$1:A1)),1)}

and
{=INDEX(B$127:B$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$127)+1),ROWS(A$1:A1)),1}

Works like a dream. Thank you for your help.
Ben Bulloch

Why do you need such? Does the real data starts at D128? Also, in which cell did you enter these formulas?

 

[benbulloch]

Re: output 1st, 2nd, 3rd ... non-zero value in a column (or row)

I realized that I could just use:
{=INDEX(D$127:D$184,SMALL(IF(D$128:D$184>0,ROW(D$128:D$184)-ROW(D$126)),ROWS(A$1:A1)),1)} (without the + row offset)

With your original formulas, I was picking up the row before data so formula was returning zero.

Data starts on D127. Does this make sense? Been staring at it too long... Formula input into F136:F146 for dollars and G136:F146 for dates.

Follow-on question:
How would you go about doing the same thing for across the row? Change ROW & ROWS to COLUMN & COLUMNS? Change the absolute references ($'s)?

This is tremendously helpful. Should be generically useful to others as well.

Cheers,
BB

 

[AdamL]

Re: output 1st, 2nd, 3rd ... non-zero value in a column (or row)

Should be generically useful to others as well.

Glad it's worked out for you Ben. But as Aladin alluded to (I didn't look carefully enough at your original formulae which I copied and pasted), a better generic description might be:

=INDEX(ReturnRange,SMALL(IF(LookupRangeCondition,ROW(LookupRange)-ROW(StartOfLookupRange)+1),ROWS(A$1:A1)),1)

so therefore

=INDEX(D$127:D$184,SMALL(IF(D$127:D$184>0,ROW(D$127:D$184)-ROW(D$127)+1),ROWS(A$1:A1)),1)

(would this be OK Aladin?)

 

[sfshah]

Taking this issue further, How to get the similar result in a row, I mean when the source range is a row, how to find the non zero values in such a case.

Further, when the source data is containing texts and zeros, how to get the text contents omitting the zero value cells of the row.
Satish

 

[Aladin Akyurek]

Taking this issue further, How to get the similar result in a row, I mean when the source range is a row, how to find the non zero values in such a case.

Further, when the source data is containing texts and zeros, how to get the text contents omitting the zero value cells of the row.
Satish

Consider A2:N2...

A4, just enter:

=COUNTIF(A2:N2,">0")

A5, control+shift+enter, not just enter, and copy across:

=IF(COLUMNS($A$5:A5)<=$A$4,INDEX($A$2:$N$2,
   SMALL(IF(ISNUMBER($A$2:$N$2),IF($A$2:$N$2>0,
    COLUMN($A$2:$N$2)-COLUMN($A$2)+1)),COLUMNS($A$5:A5))),"")

This lists all numbers from A2:N2 greater than zero in a horizontal range.
If you want the list laid-out vertically, change the COLUMNS($A$5:A5) bit to ROWS($A$5:A5) and tcopy the formula downwards.

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))),"")

 

[sfshah]

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

 

[T. Valko]

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]
To extract the TEXT entries...
 
Change the formula in A4 to:
 
=COUNTIF(A2:N2,"*")
 
Enter this array formula** in A5 and copy across until you get blanks:
 
=IF(COLUMNS($A5:A5)>$A4,"",INDEX(2:2,SMALL(IF(ISTEXT($A2:$N2),COLUMN($A2:$N2)),COLUMNS($A5:A5))))
 
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.