VBA If, than, priority delete rows help

[alieisx]

Hi!

I'm trying to delete rows but there is a priority... The file size is 33,000 lines+

The columns look like this.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]DUNS[/TD]
[TD="width: 64"] Maintenance Status[/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD] Active[/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1012319[/TD]
[TD] Active[/TD]
[/TR]
</tbody>[/TABLE]

What I need to end up with is only one line per DUNs number with "Active" if its available while deleting any other duplicate active and blank lines. Maintenance Status only has the options of : Active, Inactive, ""

I've been messing with functions and different remove duplicate methods but with being required to do this now almost daily having a macro would save a significant amount of time.

 

[alieisx]

Currently my order of operations is:

- Click "remove duplicates" selecting both columns
- Then use =COUNTIF($A:$A,A2)>1
- Take all the false and move them to a new page.
- Then sorting column B A to Z and then sorting column A, A to Z.
- The advanced filter unique values
- Cut, Paste to other page, done.

I could write a macro like this but it seems like there is a lot of room for error and there has got to be something easier...