+ Reply to Thread
Results 1 to 8 of 8

Compare two databases and place any differences in third database.

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Compare two databases and place any differences in third database.

    Hi guys

    I have just started work as a production manager for a new company and I am in the process of getting things organised.

    One of jobs that takes about an hour to currently do I believe could be done in minutes.

    Essentially we have to daily check the "live orders" on "operations" and see if any new ones have been added. If they have we need to manually import them into out spreadsheet list and plan the production in.

    It would appear from talking to the IT guy that it is impossible to add an extra column to the operations software that simply says new or gives the actual date added.

    My team therefore have to waste time manually cross referencing.

    However, I am trying to solve the problem another way using excel.

    I have uploaded a very simplified visual representation of the problem and I hoping one of you Excel hyper intelligent mega beings can help me out :o)


    The info below relates to that simplified sample I have uploaded.

    This information is drawn from some operations software that the whole company works

    A = Database 1 - is the Excel document from the day before that was manually checked the day before.

    B = Database 1 updated - is a data only spreadsheet that I export from the company operations software which potentially will have had jobs added.

    C = Desired results - I would like this to list any differences between A & B


    My thinking is that I have spreadsheet with three identically formatted spreadsheets as shown on the sample uploaded.
    I will then copy the data from our main production spreadsheer that was manually checked the day before and paste it into the database 1 area "A"
    I will then paste the exported data containing additional jobs from our operations software into the area in the red box "B"

    Ideally I want to enter a formulae into the job number row of database "C" that will look for the job number on that same row in database "B" and compare that to the entire contents of the job number column in Database "A"

    I would then like it to import all of the data on the row of any job numbers that are not present in Database "A" into the results sheet "C". It doesn't have to be in series just appear there so I can then copy and past it into my main production spead sheet.

    I can then do this daily and not have to spend about 45 minutes cross referencing everything to establish the new orders that have come in.

    Database problem.xlsx

    Thanks Guys

    Martyn

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Compare two databases and place any differences in third database.

    Does the attached help? Be aware it uses array formulas, so it might slow things down on large lists
    Attached Files Attached Files

  3. #3
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Compare two databases and place any differences in third database.

    Something like this
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Re: Compare two databases and place any differences in third database.

    I tell you what, you guys are just awesome.

    That must have took some time to sort out and you did it for no other reason tham to help another excel enthusiast


    I've had a look at both solutions and as far as I can tell they seem to work so many thanks for that.

    My problem now is understanding what you did and adjusting it to the actual working databases.

    Normally I can figure it out but these formulaes are way above what I have done in the past. It's made me feel a little inadequate if I am honest LOL.

    I'll keep trying to figure this out but if anyone of you kind souls can spare the time I have uploaded the actual database format.

    Hopefully one of you can set it up for me so I'll be able to start using it Monday and save my team and I alot of wasted time.

    Moving forward I want to study what you do and gain more of an understanding of some of the terms you used so hopefully I can get my head around it and do it myself next time.

    If it is too much work, I completely understand. I am grateful for the effort you guys have already put in.

    Database problem full.xlsx


    Sincerely

    Martyn

  5. #5
    Spammer
    Join Date
    02-04-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2013
    Posts
    190

    Re: Compare two databases and place any differences in third database.

    Hey Martin
    I can't explain how the database works, because my english is too bad, but i send you a possible solution

    greetings, JP
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Re: Compare two databases and place any differences in third database.

    Quote Originally Posted by Jean.P28 View Post
    Hey Martin
    I can't explain how the database works, because my english is too bad, but i send you a possible solution

    greetings, JP
    Jean, I really don't know what to say to express my gratitude for you sorting this out.

    This works perfectly. I have already imported it into the relevant workbook and I know there will be other Managers keen to start making use of it.

    All I can say is you are awesome and thank you very very much for helping me with this.

    I now have a whole 45 minutes extra every day to do something more constructive and as any Manager knows that is a long time.

    Sincerely and gratefully

    Martyn

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Compare two databases and place any differences in third database.

    Be aware that the solution provided contains lots of array formulas. If your database gets large, calculations might get very slow.
    If that happens, don't hesitate to come back, the solution can be trimmed down once the "ijn" is obtained, using non-array formulas

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    england
    MS-Off Ver
    Office 2013
    Posts
    55

    Re: Compare two databases and place any differences in third database.

    Quote Originally Posted by Pepe Le Mokko View Post
    Be aware that the solution provided contains lots of array formulas. If your database gets large, calculations might get very slow.
    If that happens, don't hesitate to come back, the solution can be trimmed down once the "ijn" is obtained, using non-array formulas
    Will do mate.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare and Identify Differences
    By sswood101 in forum Excel General
    Replies: 7
    Last Post: 09-07-2010, 03:11 PM
  2. Excel 2007 : Complex Database,producing Lists and Databases
    By 1Soldierofallah in forum Excel General
    Replies: 0
    Last Post: 05-13-2009, 04:17 PM
  3. Compare and report differences.
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-09-2005, 11:25 AM
  4. Compare 2 columns to see differences
    By JE McGimpsey in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 07:05 AM
  5. Compare 2 columns to see differences
    By Bugaglugs in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1