Removind row where next is almost equal

[manrique83]

I have the following rows

Account Tin Action
111111 4444444
111111 4444444 disregard
111111 4444444 checked

I need to keep rows where there is no action. If there is an action and it is fixed, I need to keep that one instead. I have come up with the programming logic, but my macro is getting nowhere.

Here is the pseudo code.

Read record
Save as old
Read next record
Save as new
If new record (Account and TIN) = old record (Account and Tin)
if new record (Action) = "checked"
keep new record
delete old record
else
keep new record
delete old record
End if


the records are sorted and will always be " ", "checked", "fixed

 

[SkipVought]

Hi,

I'd use the AutoFilter to display the row(s) to be deleted, and delete visible rows.

Do it manually to figure out what workd.

Then macro record it.

Post back your code if you need help cleaning it up.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[manrique83]

Thanks SkipVought,

But all records are unique already, except that I am trying to keep only one record where Account and Tin match. If the action is "checked", keep that one, else if the action is "disregard", keep that one.
I am not familiar with Excel VBA so my macro isn't even useful. can you provide hints?

Thanks alot

 

[SkipVought]

???

Filter on Action

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[manrique83]

Dont know what you mean? Im a giving a confusing explanation?

 

[SkipVought]

Are you in Excel or Access?

If Access, then post this in forum705

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[manrique83]

Im in excel and i would like a sample macro that would do what i have provided.

 

[SkipVought]

Activate your sheet.

Activate HELP

Read up on AutoFilter!

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}