remove groups of rows that don't meet a qualifiction

[kleptilian]

I have an excel sheet that is about 100,000 rows. I'm trying to pull out any Code that has a PAU Coverage and either a HO3 coverage or HO6 coverage. I coded the coverages as keys to try and help. 1 is for PAU and 2 is for HO3 and HO6.

I tried creating a pivot table for it but could not figure out how, I think that may be to complex to complete with Pivots.

[TMS]

Why not just turn on Autofilter, filter on the Coverage column and select the codes you don't want. Select the visible rows except the header and use the Delete icon on the ribbon to remove them.

Suggest you make a backup first and make judicious use of Undo where necessary.

Regards, TMS

[JOHN H. DAVIS]

In your after sheet you have codes which do not reflect your criteria in Column D? Am I missing something? Or does this help?

Please Login or Register  to view this content.

[kleptilian]

Forgot to clarify that. If the code has PAU and either HO3 or HO6 I want to keep everything from that code that meets the criteria.
That's why on after there are coverages that don't match PAU HO3 or HO6, because the code meets the criteria everything from the code stays.

Your macro does what I first asked, however are you able to modify it a little?

[JOHN H. DAVIS]

It can be modified. How do you want it too be modified. Add the other criteria in the After sheet?

[kleptilian]

It can be modified. How do you want it too be modified. Add the other criteria in the After sheet?

Can you modify it so that it doesn't remove the rows (other criteria) that are in the after sheet? For example all rows that have the code AUGELMIC should stay because somewhere AUGELMIC has both PAU and either HO3 or HO6(in this case AUGELMIC has both HO3 and HO6).

[JOHN H. DAVIS]

That can be done. You need to restate your criteria for the rows not too delete. There are still many items contained in your After sheet that do not meet the criteria's you've previously stated.

[kleptilian]

I'm looking to remove any code that does not have PAU coverage and either HO3 Coverage or HO6 coverage anywhere on that code. If the Code contains PAU and either HO3 or HO6 then all of that particular code should remain even if there is a coverage that does not match PAU HO3 or HO6.
On the attached example i highlighted the groups that need to be deleted. The highlight sections for the groups only contain a key of 1 or 2 or neither. The groups that remain have both a 1 and 2 key.

Let me know if I'm doing a bad job at explaining this.

[JOHN H. DAVIS]

I'm afraid I still don't understand your criteria based on your sample. The some of the highlighted cells have HO6, HO3, PAU, but then you're saying they should be deleted? However this will delete the rows which are highlighted. Hope that helps.

Please Login or Register  to view this content.

[TMS]

You could put thie following formula in cell G2 and copy it down.

Please Login or Register  to view this content.

Then you can filter on column G for "keep" or "delete". It gives you the opportunity to sight check data. Note you have "DIPALMAR" on row 64 which perhaps should be "DIPOLMAR". Maybe safer than running code blind.

That said, it would be easy enough to add that formula using code and do the filter, selection and deletion of rows.

See the attached updated example.

Regards, TMS

[kleptilian]

I'm afraid I still don't understand your criteria based on your sample. The some of the highlighted cells have HO6, HO3, PAU, but then you're saying they should be deleted? However this will delete the rows which are highlighted. Hope that helps.

Some of the highlights do have either PAU HO3 or HO6, that is correct. What i want to keep needs to have both PAU and HO3 or both PAU and HO6.
123OLDYO gets deleted because it only has HO6
AARONMAT gets deleted because it only has HO6
ABADAMAR gets deleted because it only has HO3
ABATEKAT does not get deleted because it has both PAU and HO3
ABBOTCYN gets deleted because it onlyu has PAU
All of AUGELMIC Stays because it has both PAU and HO3 it also has HO6... This would only be removed if AUGELMIC did not have HO3 and HO6or if it did not have PAU.

[kleptilian]

You could put thie following formula in cell G2 and copy it down.

Please Login or Register  to view this content.

Regards, TMS

This accomplishs what I need. Thanks for your help.

Also Thanks to John for working with me and trying to get this figured out.

[TMS]

You're welcome.

[JOHN H. DAVIS]

You're welcome. Glad you got a solution.