Autcopy selected details to another worksheet, without blank rows between items

[Michael Jones]

I have data as in the example below:


[TABLE="width: 359"]
<tbody>[TR]
[TD]Investment
[/TD]
[TD]Value
[/TD]
[TD]Advice required?
[/TD]
[/TR]
[TR]
[TD]Barclays
[/TD]
[TD]£100,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Ist National
[/TD]
[TD]£50,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Buy-to-Let Property
[/TD]
[TD]£325,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Sky TV Shares
[/TD]
[TD]£1,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]ING Investment Bond
[/TD]
[TD]£50,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]ICI Shares
[/TD]
[TD]£30,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]MG Investment Bond
[/TD]
[TD]£30,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Prudential Savings Bond 1
[/TD]
[TD]£30,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Prudential Bond 2
[/TD]
[TD]£100,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]£716,000.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where the answer in column C has been answered "Yes" I want to have this autocopied to another worksheet to produce the following, without any blank rows:


[TABLE="width: 359"]
<tbody>[TR]
[TD]Investment
[/TD]
[TD]Value
[/TD]
[TD]Advice required?
[/TD]
[/TR]
[TR]
[TD]Barclays
[/TD]
[TD]£100,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Ist National
[/TD]
[TD]£50,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Buy-to-Let Property
[/TD]
[TD]£325,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Sky TV Shares
[/TD]
[TD]£1,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]ING Investment Bond
[/TD]
[TD]£50,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]ICI Shares
[/TD]
[TD]£30,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]MG Investment Bond
[/TD]
[TD]£30,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Prudential Savings Bond 1
[/TD]
[TD]£30,000.00
[/TD]
[TD]No
[/TD]
[/TR]
[TR]
[TD]Prudential Bond 2
[/TD]
[TD]£100,000.00
[/TD]
[TD]Yes
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD]£716,000.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone solve my problem? Many thanks in advance

Kind regards

Michael

 

[estevaoba]

Array enter this and copy across and down:

[TABLE="width: 64"]
<tbody>[TR]
  [TD="width: 64"]=IFERROR(INDEX($A$2:$C$1000,SMALL(IF($C$2:$C$1000="Yes",ROW($C$2:$C$1000)-1),ROW($A1)),COLUMN(A$1)),"")[/TD]
[/TR]
</tbody>[/TABLE]