Copy whole row into different sheet if specific text found in Column.

[strongman86]

Lads,
Hope you can help me. I have Table in Sheet1 consisting of 100's of rows and about 20 columns similar to below. I need formula in Sheet2 that looks through Column A Sheet1 and if it finds
APPLE IN CELL then it needs to copy whole row into Sheet2 and same for PEAR. This need to be in the order they appear in Sheet1. Thanks.


Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]APPLE[/TD]
[TD]UK[/TD]
[TD]10[/TD]
[TD]WEEK1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BANANA[/TD]
[TD]SPAIN[/TD]
[TD]15[/TD]
[TD]WEEK2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ORANGE[/TD]
[TD]SPAIN[/TD]
[TD]20[/TD]
[TD]WEEK2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]APPLE[/TD]
[TD]IE[/TD]
[TD]15[/TD]
[TD]WEEK1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]LEMON[/TD]
[TD]US[/TD]
[TD]10[/TD]
[TD]WEEK3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]PEAR[/TD]
[TD]US[/TD]
[TD]20[/TD]
[TD]WEEK5[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]APPLE[/TD]
[TD]UK[/TD]
[TD]10[/TD]
[TD]WEEK1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]APPLE[/TD]
[TD]IE[/TD]
[TD]15[/TD]
[TD]WEEK1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]PEAR[/TD]
[TD]US[/TD]
[TD]20[/TD]
[TD]WEEK5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

So it just looks for APPLE or PEAR in Sheet1, nothing else?

 

[strongman86]

Yes mate.

 

[Special-K99]

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$D$6,AGGREGATE(15,6,ROW($A$1:$D$6)/((Sheet1!$A$1:$D$6="APPLE")+(Sheet1!$A$1:$D$6="PEAR")),ROWS(A$1:A1))-(1-1),COLUMN()),"")
copy down for as many rows as you have on Sheet1 and across to column D

 

[strongman86]

Thank you Special-K99