Updated List - Finding the differences and similarities

[VBA Beginner]

Hello all,

At least 3 times a week, I run a query in Access that updates a contract database. I then take this information and paste it into Excel. Some contracts are dropped out and some are added. What i have been doing to find out which ones are added and dropped is the following: I sort each of the lists by contract ID, then I do a vlookup on both of the lists to see which contract ID's are not in the other. I add another column and add something like FALSE in the ones that don't have the contracts, then I sort by them and seperate them out. Can anyone think of a better way to do this? It takes quite awhile, as it is a lot of data I am working with (almost 60,000 rows) Thank you.

Chris

[Bryan Hessey]

Hello all,

At least 3 times a week, I run a query in Access that updates a contract database. I then take this information and paste it into Excel. Some contracts are dropped out and some are added. What i have been doing to find out which ones are added and dropped is the following: I sort each of the lists by contract ID, then I do a vlookup on both of the lists to see which contract ID's are not in the other. I add another column and add something like FALSE in the ones that don't have the contracts, then I sort by them and seperate them out. Can anyone think of a better way to do this? It takes quite awhile, as it is a lot of data I am working with (almost 60,000 rows) Thank you.

Chris

Hi,

it should take about 30 seconds to do that.

Firstly the sorts are irrelavant unless you need a final sort to arrange the newly merged file.

Set the VLookup to allow for blank rows (so that you can apply the formula to the whole column), with the 'missing' option as you are doing.

Put the formula in row (say Z column) Z2 and Copy the formula.

Enter Z2:Z65536 in the NameBox and press Enter to select the column, then Paste.
This should formula Fill to the bottom.

If you need you can then copy the column and Paste Special = Values back over itsself to remove all formula and just leave 'missing' and blanks.

Put a title in row 1 of that column and apply Data, Filter, Autofilter, then dropdown and select the 'missing'.

Select all displayed rows and in one case Delete (deleteds), in the other case Copy and Paste to your regular sheet (New Items).

Delete the helper column.

Then sort if required.

Let me know how you go.

note, some of this could be recorded in a Macro and the Shortcut keys used.

---