[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Looking for suggestions (What application to use)?

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.
Starting-File.xlsx
Updated-Weekly-File.xlsx
0
ArisaAnsar
Asked:
ArisaAnsar
1 Solution
 
Eric ShermanAccountant/DeveloperCommented:
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.

ET
0
 
ArisaAnsarAuthor Commented:
Thank you!  This is a great idea.  I sent you a separate email as well.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now