copy all rows with a certain value in one of the columns

[emmerdales]

Hi,

I run my companies sales tracker on Excel, and I am trying to populate a list of people with zero sales for the week etc.

[TABLE="width: 500"]
<tbody>[TR]
[TD]employee[/TD]
[TD]target[/TD]
[TD]actual[/TD]
[TD]vstgt[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]julie[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD]sarah[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

and it auto fill a section of zero sellers, so populate a list of names with a 0% in the vstgt column.

so I want:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Zero Sellers[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]and auto add more here as the figures change[/TD]
[/TR]
</tbody>[/TABLE]


I will try and upload a sample file.

If anyone would be able to assist I would be very grateful.

thanks

 

[jarjarbingie]

Hi,

I run my companies sales tracker on Excel, and I am trying to populate a list of people with zero sales for the week etc.

[TABLE="width: 500"]
<tbody>[TR]
[TD]employee[/TD]
[TD]target[/TD]
[TD]actual[/TD]
[TD]vstgt[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]julie[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]33%[/TD]
[/TR]
[TR]
[TD]sarah[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

and it auto fill a section of zero sellers, so populate a list of names with a 0% in the vstgt column.

so I want:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Zero Sellers[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]and auto add more here as the figures change[/TD]
[/TR]
</tbody>[/TABLE]


I will try and upload a sample file.

If anyone would be able to assist I would be very grateful.

thanks

assuming table1 is in Sheet1 and table2 is in Sheet2:

A2 = IFERROR(INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$D$2:$D$100=0,ROW(Sheet1!$D$2:$D$100),""),ROW()-1)-1),"") press CTRL+SHIFT+ENTER