VBA for Sheet Comparison

[gregtgonzalez]

Hello,
I am working on a project that I would like some help on, i have a list that is delivered at 9 am everyday to the same file location. The list is formatted the same everyday, however there are changes. I need to find and record the changes between each day the list is generated. What i would like some guidance on is creating a vba program that would allow the user to choose any 2 reports on the list and be able to see the differences between one and the other.
I am much better with building queries, and data visualization but this is something that i would benefit from learning.

Any guidance or suggestions would be appreciated.

If this helps in each list is quite large nearly 19k rows and nearly 10 columns.

 

[Joe4]

Quite frankly, for something of that magnitude, I think Microsoft Access may be a better option. From my experience, it seems to handle large comparisons better than Excel.
I think that you would need so many Index/Match or Vlookup formulas in Excel, performance would be a major factor.

Access also has a built-in Unmatched Query Wizard, which you can use return all the records from one list that does not match the other list. The Wizard itself only allows you to pick one field to match on, but after you create it using the Wizard, you can open it up in Query Design View, and create joins for all the other fields, so you are matching on all fields.

Basically, you would do this twice, one to show all the records on TableA that do not have a match on TableB.
And then, vice versa, all the records on TableB that do not have a match on TableA.

If the files are already Excel files, you don't even need to import them into Access. You can simply link them to Access tables.

 

[gregtgonzalez]

thats a great suggestion Joe! i had forgotten about the unmatched query, however, my access seems to be having trouble processing the data. the query time just takes for ever. I have changed the bit version of my office program.

 

[Joe4]

Yes, depending on the size of the data (which sounds pretty large), it may be a bit much, even for Access.
If it is possible to add Primary Keys or Indexes to your Access table, that migth help, as might reducing the number of fields you are matching on, if possible.

Its possible it may need something with even more power, like SQL. I believe you can download "MySql" for free. It may be worth a try.

 

[gregtgonzalez]

to be clear the only change i would be looking for would be if a line has been added or subtracted between the two occurrences of the report being generated.

 

[Joe4]

If you concatenate all the fields to make one big long field, then you would only need to match up on one field.
Might make things a little better/faster in both Excel or Access, to try it that way.