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

Something like this

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

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

Originally Posted by Jean.P28

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

Originally Posted by Pepe Le Mokko

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.