Formula for Filter

[manjukhan]

I need to filter a column for non blanks and list the values on a
different column. I can do this by doing a autofilter and filter for
non blanks . But I want to do it automatically with a formula. Do any
one have a formula accomplish this.

Thanks
Mansoor A Khan

 

[Domenic]

Assuming that Column A contains your data, the first row contains your
header/label, and the values are text, try...

B2, copied down:

=IF(ROWS($B$2:B2)<=COUNTIF($A$2:$A$100,"?*"),INDEX($A$2:$A$100,SMALL(IF($A$2:$A$100<>"",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($B$2:B2))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. If the values
are numerical, change...

COUNTIF($A$2:$A$100,"?*")

...to...


COUNTIF($A$2:$A$100,"<0")+COUNTIF($A$2:$A$100,">=0")

Hope this helps!

 

[manjukhan]

Dominic,
Thank you very much . The formula is working fine. I will get back t
you if I have any problems.
Appreciate your help