Remove row set based on compare

[projectile]

Hello

I have this extract from msquery , and cannot find a way to exclude customers that have both to codes against their client record, SPQ & FUP for a particular date range. I cannot get the query to work, so think it best to return the figures to excel, and run a compare macro on the table.


Is it possible to do the following;

run a compare on the client column and activity column;;

If a client has has both a SPQ & FUP entry on different rows, then remove all instances of client row, then continue this down the sheet, so that all clients with both codes are removed.

e.g. if clients no. 003212 has both codes SPQ & FUP , then remove entire rows for 003212. i.e. in example below, 4 rows for INternational Ltd would be removed. In the example below, the entire rows in yellow should be removed, based on criteria above.


hope this makes sense.

 

[projectile]

Yes, the only that should remain is SPQ, which do not have a FUP or JOB.
Thanks

 

[Dannyh1]

makes it a bit easier:


=IF(ISNA(IF(C2<>"SPQ","delete",IF(ISNA(MATCH(B2&"FUP",D:D,0)),(MATCH(B2&"JOB",D:D,0)),"delete"))),"","delete")

cheers.

 

[projectile]

Hi
Couldn't that one to work, it was including activity codes that should be marked as deleted.

So i used your previous formula and added an autofilter, and appears to work, until someone spots an issue

Thanks for your help.