VBA for conditional removal of duplicates?

[scootsy]

Hi,

I am not sure if this is possible, but I am generating a report that needs duplicates removed if a certain condition is met. Namely, I have to catchall duplicates in column C, and if any of those duplicates has a certain status code in column A, all duplicates have to be removed. Is this possible?

In the sample attached, I need to delete both "A" and "B" entries because one of the duplicates has the status "1" in Column A. I can't just delete all 1's, because it would also need to delete the A and B that have value "2".

I guess another way to go about it would be to find all 1's and the delete any row that has the same name in column B? I don't know.

[PCI]

"another way to go about it would be to find all 1's and the delete any row that has the same name in column B"
Yes it is a way to do
Do you want a macro to do it or are you ready to do it manually ?

[scootsy]

If that's a possibility, I would greatly appreciate help with a macro!

[jeffreybrown]

So you are requiring the first four rows removed? You would be left with row 6 & 7?

One quick and easy way, put in C2 copied down >> =COUNTIFS(B:B,B2,A:A,1)

Now filter column c on the 1's and delete those rows. That can easily be turned into a macro, but is this the direction you desire?

[jeffreybrown]

Try this on a copy of your workbook...

Please Login or Register  to view this content.

[PCI]

Here the macro

Please Login or Register  to view this content.

[scootsy]

Yes, the first four rows would fail the condition and be deleted. All that would be left would be the last two.

And I am ultimately trying to build a macro to achieve this. A complication I may run into though is that there are actually 7-8 statuses that will trip this condition and cause the need for any duplicates to be deleted. Should I add a line to the macro before this that would rename all of the statuses to the same one?

[scootsy]

PCI,

Awesome! Thanks!!

[scootsy]

jeffreybrown,

Worked perfectly. Thanks!

[jeffreybrown]

You're very welcome. We are happy to help.

Please mark as solved.

[jeffreybrown]

A complication I may run into though is that there are actually 7-8 statuses that will trip this condition and cause the need for any duplicates to be deleted. Should I add a line to the macro before this that would rename all of the statuses to the same one?

Sorry I missed this part, but not sure I understand.