Displaying Row by Criteria

[jdot]

Hello All,

Hopefully you could assist me in solving an issue I've been having all morning!

I have a data set on Sheet 1 as follows:

Yes Bob 2 1992
Yes Rick 6 1992
Yes Steve 5 1998
No Bob 2 1992
Yes Steve 5 1992

What I want is on Sheet 2, to display this:

Yes Bob 2 1992
Yes Rick 6 1992
Yes Steve 5 1998
Yes Steve 5 1992

With no empty rows. The criteria is if column A = "Yes", then the whole row should pull in. I need a function/formula that works on Sheet 2, so when Sheet 1 data is changed, Sheet 2 automatically pulls in the Yes criteria. Any ideas? I tried a VLOOKUP and it only pulled in the first row, nothing else.

[Ace_XL]

With your data starting from Row 2 use

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

Copy down and across

[jdot]

Thanks! I had an issue with the formula running across though. Instead of pulling in:

Yes Bob 2 1992
Yes Rick 6 1992
Yes Steve 5 1998
Yes Steve 5 1992

My spreadsheet pulled in

Yes Yes Yes Yes
Yes Yes Yes Yes
Yes Yes Yes Yes
Yes Yes Yes Yes

The number of rows are correct, but the values are incorrect.

[Ace_XL]

You must have absolute referenced column B.

See formula in action (highlighted in yellow)

[jdot]

Worked like a charm! Thank you!!!