Looping and Comparing values in two workbooks

[Lynn A.]

REPOST (with more detail)

I have two files Invoices and Tracking. (Both of these are uploaded
into Access once all duplicates are removed)

Tracking
CREATEDATE INVOICENUMBER OLDCODE NEWCODE
09012004 6:00 am 0001 O P
09012004 6:03 am 0001 P CH
09012004 6:05 am 0001 CH C
09062004 12:12 pm 0002 O P
09062004 1:45 pm 0002 P CH
09172004 9:00 am 0003 0 P
09222004 11:15 am 0003 P CH
09272004 2:22 pm 0003 CH P

I cycle through and delete the duplicates by invoice number and keep
the most recent (I have a little delete duplicates code). This is
what I
end up with.

CREATEDATE INVOICENUMBER OLDCODE NEWCODE
09012004 6:05 am 0001 CH C
09062004 1:45 pm 0002 P CH
09272004 2:22 pm 0003 CH P


Invoices (this does not have a date, I have requested but again issues
with having format changed) Sometimes there are duplicates and
sometimes there isn't. I'm not sure why this spreadsheet gets a
duplicate but this is what it would look like. Sometimes there are
duplicates, sometimes there isn't. It doesn't seem to come in any
particular order. It's not always sorted by invoice number.

INVOICENUMBER CODE
0001 P
0001 CH
0001 C
0002 CH
0003 P
0003 CH


What I would like ......

I would like to delete the duplicates in the invoice spreadsheet so
that the code in the Invoices table matches the New Code in the
Tracking table.

The results I would like in the invoices table

INVOICENUMBER CODE
0001 C
0002 CH
0003 P


I am deleting duplicates from the Invoices spreadsheet (with the same
code that I use to delete duplicates from the tracking table), then I
compare the duplicated invoicenumber with the NewCode from the
Tracking table to make sure they are the same. I don't want a new
spreadsheet, I don't want to query anything. I want to DELETE
duplicates based on the match between the code in the invoices table
and the newcode in the tracking table.

Can it be done?

Thanking you in advance, Lynn

 

[Tom Ogilvy]

Use vlookup to get the new code and do your compare

res =
Application.Vlookup(InvoiceNumber,workbooks("Tracking.xls").Worksheets("Data
"),Range("B2200"),3,False)

if code <> res then