• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Required filter non blank values by formula.

Juniad

Member
Hi,
I have 5 columns where i have value X & Y and some blank cells as well. i need to filter non blank values and return the result column B to D..
 

Attachments

bosco_yip

Excel Ninja
Try,

In E3, copied across and down :

=IFERROR(INDEX(B$2:B$27,AGGREGATE(15,6,ROW($A$2:$A$27)-ROW($A$1)/($A$2:$A$27=$K$2)/(INDEX($E$2:$I$27,0,MATCH($K$2,$E$1:$I$1,0))<>""),ROWS($1:1))),"")

Regards
Bosco
 

Attachments

bosco_yip

Excel Ninja
I want to apply it on MS 2007.. Sorry for inconvenience...
For excel 2007 or below

In E3, CSE formula (confirm entered with CTRL+SHIFT+ENTER) copied across and down :

=IFERROR(INDEX(B$2:B$27,SMALL(IF(($A$2:$A$27=$K$2)*(INDEX($E$2:$I$27,0,MATCH($K$2,$E$1:$I$1,0))<>""),ROW($A$2:$A$27)-ROW($A$1)),ROWS($1:1))),"")

Regards
Bosco
 

Attachments

Top