Auto remove of Zero's using formula

[sguna1982]

Dear Team,

i am looking for formula to auto filter the to remove the "Zero" in the selected range.

Thanks in advance.
Guna.S

Excel attached.

 

[shrivallabha]

You can use approach (change condition to suit your situation) in the following thread:
http://chandoo.org/forum/threads/get-top-n-records-based-on-some-condition.18099/#post-109231

If the numeric sequence is not important then you could also do following.

Select the target range E2:E23 and then select formatting and then apply following custom formatting
0;-0;""

And then write following formula and copy down:
=LARGE($A$2:$A$23,ROWS($B$2:$B2))

 

[sguna1982]

Thanks for your quick response.

In this scenario numeric sequence is as per the order. Is this possible to get the exact formula.

I think you shared the other thread, but its not suitable for my scenario

Thanks.

 

[shrivallabha]

CTRL+SHIFT+ENTER following formula:
=IFERROR(INDEX($A$1:$A$23,SMALL(IF($A$2:$A$23>0,ROW($A$2:$A$23)),ROWS($B$1:$B1))),"")
And then copy down.

 

[sguna1982]

Excellent, Thank you so much...

Is it possible to explain the formula !!!

 

[shrivallabha]

IFERROR part is used to mask errors with blank("") which INDEX will throw up when all the values from source list are listed.

INDEX is used for returning element specified by row number, column number. Column number is optional. And it is omitted in above formula as we are dealing with single column.

Now to find out the rows where the condition is met and to list them sequentially the following construct is used:
SMALL(IF($A$2:$A$23>0,ROW($A$2:$A$23)),ROWS($B$1:$B1))
ROWS($B$1:$B1) gives 1 in first cell and generates numbers sequentially as 2,3,4,5,...as we go on copying formula down.The IF construct returns TRUE cases where the condition is met. So using this combination we get a series of rows where the condition specified is met.

Use Formulas | Formula Auditing | Evaluate Formula to see the workings of formula.