# Re: Can you display entire rows that match a certain criteria...

Pete_UK
Guest
Posts: n/a

 4th Sep 2012
Yes, you can set up a formula in a helper column of Sheet1 which
embodies the criteria that you want to apply and establishes a simple
sequence - something like this in P2 (say):

=IF(A2="cat",MAX(P\$1:P1)+1,"-")

Copy this down beyond the data that you have (the hyphens will
indicate how far you have copied it).

Then in P2 of Sheet2 you can have a formula like this to return the
rows where those criteria were identified on Sheet1:

=IFERROR(MATCH(ROWS(\$1:1),Sheet1!P:P,0),"-")

Then in A2 you can have this formula:

=IF(OR(\$P2="-",\$P2=""),"",INDEX(Sheet1!A:A,\$P2))

which will retrieve the data from column A in Sheet1. This formula can
be copied across row 2 to retrieve data from other columns, then all
the formulae in row 2 can be copied down as far as you need to (until
hyphens begin to show in column P).

Hope this helps.

Pete

On 4 Sep, 14:39, Jay07 <Jay07.a8bb...@excelbanter.com> wrote:
> Not sure if this is possible so just want to throw the question out to
> you guys.
>
> If I've got masses of data in say sheet1, could I in sheet2 use any kind
> of formula that would display every row of sheet1 where that meets a
> condition.
>
> i.e...
>
> In Cell A1 of Sheet2 :
>
> IF('Sheet1'A1=>1, **DISPLAY HOLE ROW**)
>
> This could result in high number of matches for what I'm trying to do
> but is there any way that Excel could display all of these in rows
> B2:B*HOWEVER MANY MATCHES**
>
> Thoughts?
>
> Cheers.
>
> --
> Jay07

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Scott Microsoft Excel Misc 4 11th Dec 2009 06:50 AM Steve Microsoft Excel Discussion 2 2nd Dec 2005 12:33 AM Nan Microsoft Excel Programming 1 12th Jul 2004 05:04 PM Thorsten Matzner Windows XP New Users 0 5th Jun 2004 04:34 PM =?Utf-8?B?Y2hyaXM=?= Windows XP General 1 3rd Jun 2004 08:17 PM

Features