Matching two databases

[Carlos2229]

I am working with a couple of databases that I have to compare and look for differences. I am trying to insert a batch number that exists in one database but not the other. The databases have dates, ID’s and amounts. There is a one day lag in the dates and there are multiple amounts on one source that add up to one amount on the other. In addition there are duplicate amounts that occur on different days. Vlookup stops looking after it finds the first number. Each database contains about 30,000 lines. Below is a very basic sample. Your input is appreciated. Thanks in advance.

Date Id Amount
1/5/2015 12345 20.00
1/6/2015 12345 20.00
1/30/2015 12345 20.00
1/31/2015 12345 20.00




Date ID Batch number Amount
1/4/2015 12345 1 40.00
1/29/2015 12345 2 20.00
1/30/2015 12345 3 20.00

 

[Chihiro]

If format is always same, you can use Text functions to extract ID portion.

Something like
=LEFT(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),5)

P.S. Sample file with enough data and expected outcome will help you get the right answer.

 

[Carlos2229]

If format is always same, you can use Text functions to extract ID portion.

Something like
=LEFT(RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)),5)

P.S. Sample file with enough data and expected outcome will help you get the right answer.

Thanks for your reply. I am trying to import the batch number from one database to the other. I concatenate the Id and the amount so that I can match up most of the information but then the duplicates and the multiple amounts adding up to one number become an almost manual process.

 

[Chihiro]

Hmm, can you upload sample raw data along with expected outcome?
Databases should have unique key which can distinguish between items.