Customized vlookup to account for duplicates / errors

[mrangel]

Hello all,
I am begging you all to help me with this issue I have been having. I have been racking my brain for a while now to figure out what kind of manipulation I would have to make to a given formula in order for my spreadsheet to automate a reconciliation process I do on a daily basis. At times the process can take up to 2 hours given a massive amount of transactions in the prior day, but if I were to figure out how to use the correct formula, it would only take me a matter of seconds. I have tried vlookup, if, and other functions and I have not been able to get the results I need. Maybe the formulas were formatted wrong, but that is why I am asking for your alls help!!! So, here is the situation.
I have 2 spreadsheets, with totally different formats I must work with. In order to do it manually I print one out and look up the correct information in the excel spreadsheet of the other one. This is getting old… very very old, and I KNOW for a fact there is a way to do it with excel I just can’t figure it out.

If the tables below do not show up correctly, I have attached the spreadsheet with the given information in it. Let me know if you need anything else!


Sheet 1
A B C D E F
ABC Company
Purchase and Sale Report
Mr. Smith
Account Number: 123456
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 150 Oranges 2.1345 320.18
5/18/2009 5/18/2009 445 Bananas 2.9865 1328.99

------------
1649.17
ABC Company
Purchase and Sale Report
Mr. Marcus
Account Number: 234567
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 134 Lemons 1.5736 210.86
5/18/2009 5/18/2009 445 Bananas 2.9865 1328.99
------------
1588.66


ABC Company
Purchase and Sale Report
Mr. Thomson
Account Number: 345678
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 227 Limes 2.0345 461.83
------------
461.83
ABC Company
Purchase and Sale Report
Mr. Grant
Account Number: 456789
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 445 Bananas 2.9865 1400.00
------------
1400.00
ABC Company
Purchase and Sale Report
Mr. Stone
Account Number: 567890
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 400 Strawberries 3.4445 1377.80
------------
1377.80
ABC Company
Purchase and Sale Report
Mr. Fernandez
Account Number: 678901
Report as of 5/18/09

Trade Settle
Date Date Quantity Item Price Net Amount
----------- ----------- ----------- ----------- ----------- -----------


Purchase
------------
5/18/2009 5/18/2009 575 Cantaloupe 3.0245 1739.09
------------
1739.09
Sheet 2
A B C D E F
Item Name Salesperson Price Account Name/ Number Quantity Net Amount
Oranges Mike 2.1345 Mr. Smith- 123456 150 320.18
Bananas Mike 2.9865 Mr. Smith- 123456 445 1328.99
Limes Mike 2.0345 Mr. Thomson- 345678 227 461.83
Bananas Mike 2.9865 Mr. Grant- 456789 445 1328.99
Strawberries Bill 3.4445 Mr. Stone- 567890 400 1377.80
Lemons Mike 1.5736 Mr. Marcus- 234567 134 210.86
Bananas Mike 2.9865 Mr. Marcus- 234567 445 1350.00
Cantaloupe Bill 3.0245 Mr. Fernandez- 678901 575 1739.09

Ok, so there is the fictitious raw data. The goal of this process is to make sure that both spreadsheets are running off the same information, due to them being produced by two totally different systems (Ending Net Amounts Match). I felt the easiest way to do this is to run a vlookup function using the Net Amounts of both spreadsheets. However, being that the standard vlookup function does not account for duplicates, this method is not correct. I.e. Mr. Smith buying the same amount of bananas as Mr. Grant, and Mr. Marcus at the same price. If the vlookup was installed here, it would come back stating it is correct because the value of 1328.99 exists. This 1328.99 in sheet 1, being the first value of 1328.99 in column F in Spreadsheet 2, being the net amount of Mr. Smith’s banana purchase.
Problem:
Mr. Marcus in sheet 1-> banana purchase row-> yields 1328.99
If vlookup were used, it would say the condition is true b/c of Mr. Marcus’s 1328.99 value in sheet 2. This would have been an error b/c Mr. Marcus’s true value was 1350 ( an error in itself, but you get the point) I would need the function to return that value of 1350, not anything else. To avoid this problem, I feel that if there is a way to include the exclusion of duplicate values once they are used in a vlookup function that would be the way to go about this problem.

If you guys need any other information from me PLEASE let me know. I would really appreciate it if any of you can help me out on this project. Thanks again! I attached the excel spreadsheet so you can play around with it.

[jzibton]

How much data is there? Any chance to use multiple steps to prep the data to allow easier comparisons? And how consistent is the formating of the data?

[mrangel]

As far as the data goes, it depends on the day.

However, the format is always consistent with the sample numbers I have posted here.

Using multiple steps could be doable, but I feel that it might be a bit cumbersome if there is an abundant amount of data.

Please let me know if you need anything else! Thanks again!