find the latest row based on ID and date submitted

So lets say I have about 10000 rows of data or more. The columns have I have Date, Code, ID, billed amt, and Paid amt. I have ID number which can be repeated number of times, and I want to find the row where the ID and code came through for the last instance. Want to delete the older ones. Also there might be some code and id that were never repeated, so i would want to keep those.

If the data is always in date order in column A then you could use a formula like the one below if F2 and copied down:

Formula:

Please Login or Register  to view this content.

It will identify the ones that can go and the ones to keep.

BSB

My suggestion is a macro. Insert my code into the VBA module and run.

Please Login or Register  to view this content.

Regards.

I have over 800000 rows of data. Excel dont do the function for all. Any other suggestion?

Originally Posted by BadlySpelledBuoy

If the data is always in date order in column A then you could use a formula like the one below if F2 and copied down:

Formula:

Please Login or Register  to view this content.

It will identify the ones that can go and the ones to keep.

BSB

In your first post you mentioned around 10k lines. 800k lines is a bit of a step up from there.
Perhaps if you'd mentioned that in the first place I'd have come up with a different solution!

Have you tried the VBA approach provided?

BSB

just got the new file. see attached. field names have changed and also the number of columns. can you help modify your macro accordingly. Date = Batch date; code = CPT; and ID = Account

tried to modify vba code, but not familiar with it on how to

out of memory error

@yaksh420, test my modyfied code.
The results are in sheet 2.

Please Login or Register  to view this content.

Regards.

getting out of memory error