Delete row if the fruit has 1 and 0

[Tedishere]

I have the following excel content:

Fruit In/Out
Apple 1
Orange 1
Watermelon 1
Banana 1
Apple 0
Orange 0
Peach 1
Apple 1

I want the final result to look like:

Fruit In/Out
Watermelon 1
Banana 1
Peach 1
Apple 1

What I want is to delete every fruit that has been "In" as well as been "Out". For ex: Apple has been In (1) and Out (0), and it has been In again, but don't delete that because it hasn't been Out.

I am not sure how to approach a problem like this.

 

[NARAYANK991]

Hi ,

If you want rows to be deleted , only VBA will do the job.

If you want data to be extracted to a new sheet , even formulae can be used.

Either way , please upload your workbook with adequate data in it.

Narayan

 

[Tedishere]

Hi ,

If you want rows to be deleted , only VBA will do the job.

If you want data to be extracted to a new sheet , even formulae can be used.

Either way , please upload your workbook with adequate data in it.

Narayan

 

[bosco_yip]

Formula way to extract "IN" data.

1] In helper C2, formula copy down :

=IF(COUNTIF(A$1:A1,A2)>0,"",IF(SUMPRODUCT(($A$2:$A$9=A2)*SUBSTITUTE(B$2:B$9,"0","-1"))=1,MAX(C$1:C1)+1,""))

2] In "Output" E2, formula copy down :

=IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($1:1),$C$2:$C$9,0)),"")

3] In "Output" F2, formula copy down :

=IF(E2="","",1)

Regards
Bosco

 

[Tedishere]

Wow! Thanks a lot.