Looking for suggestions (What application to use)?

Posted on 2014-08-05
Last Modified: 2014-08-05
I have two excel files (both have approximately 65000 rows).
One is my base file (starting file) and each week I received an updated version.  My goal is to compare the two files to see if any new accounts was added or deleted.  In addition, if the accounts remain on the file each week, check if there have been any changes to the value in column E-H.

It takes two much time to do this manually.  Does anyone have any suggestion how to process this quicker?
I'm afraid excel VBA will take 4 hours if I run in excel VBA.
Question by:ArisaAnsar
    LVL 19

    Accepted Solution

    My recommendation would be to use Access with a VBA Function.  Create two tables (tblCurrentWeek, tblPreviousWeek)that match your Excel weekly file.  Create a VBA Function to import your weekly worksheet and have it FIRST delete tblPreviousWeek and move tblCurrentWeek records to tblPreviousWeek.  Delete all  records in tblCurrentWeek and import your worksheet.  Then have the function run unmatched queries to find new accounts and deleted accounts (I would pull them into separate tables tblNewAccount and tblDeleteAccounts).  Then compare the values in fields E-H and mark the records in tblCurrentWeek if anything has changed in a field name RecordWasChanged (Yes/No).

    Just an idea.


    Author Closing Comment

    Thank you!  This is a great idea.  I sent you a separate email as well.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now