Revenue on books for 5/5/2016
Site 6/1/2016
6/2/2016
6/3/2016
6/4/2016 30 1,200 1340 1290 1100 25 1265 1567 1800 2110 56 1300 1400 1350 1375
I have a folder with lots of what I call a "snapshots" in excel format. The snapshot is an excel file that is saved showing what the revenue on the books for a particular day. So in the above table you can see an example of a snapshot taken on May 5th, 2016 that shows how much revenue is already scheduled for 6/1 - 6/4. This allows us to see day to day pickup in revenue. The problem is there are now 2300 of these excel files.
When I want to analyze these my current solution is to use Excel and VBA to automate grabbing the files and inputting the data into my Excel Model. The issue that is happening now is that the excel file has over 700,000 Vlookup Match formulas and it is taking an extremely long amount of time to update the file when I want to compare two dates.
I have not used Access in my current or previous role so I a not sure the best way to go about uploading 2300 files to an excel database. I can use VBA to fix the file format in anyway I need to just kind of looking for suggestions on how anyone would go about this project.
Any advice is much appreciated.
Thank you.