Invoice Comparison help

[MichaelA]

Hello,

This is my first time actually posting anything, but this site has been a tremendous help to me and my career. Today I find myself in a situation in which I seem to not find the solution. Knowning how the people who assist in this forum are elite in their skills, I figured I asked.

So we have two invoices, one that shows our companies record of transactions and the other invoice is one created by the company we pay per transaction. Now the problem is that our invouice and theirs are having discrepencies, costing us over $20,000 a month.

The thing is that the same account could have multiple transactions for different amounts and obviously different dates. We tried Vlookup but as you all we'll know, Vlookup only retrieves the first value that matches.

Example:

Their List shows:

Column C(Account Number) Column D(Amount)
C1: 123 D1: $5.00
C2: 431 D2: $10.00
C3: 111 D3: $2.00
C4: 122 D4: $6.00
C5: 123 D5: $6.00
C6: 431 D6: $10.00

Our List shows:

Column C(Account Number) Column D(Amount)
C1: 542 D1: $3.00
C234: 431 D234: $7.00
C254: 431 D234: $10.00

Vlookup pulls the first match but the value it brings to us is the wrong one because the same account is has other values in the list. You really cant sort them on the count that the dates are different, there already is discrepencies and some accounts are on our list that are not in theirs and vice versa.

What we need is something like an Arbritrary LookUp, but the ones that I have found thus far are either too complicated/don't work.

Someone help please. What can we use to find the discrepencies, with multiple/duplicate values?

 

[MichaelA]

Quick bump

 

[crunchtime9999]

Sum the total dollars by invoice (yours and theirs) and then perform the vlookup. This will at least show you which invoice(s) don't match. You can also concatenate the invoice number and amount to make a unique lookup key (assuming that invoice numbers and totals aren't repeated)

Good luck

 

[MichaelA]

Sum the total dollars by invoice (yours and theirs) and then perform the vlookup. This will at least show you which invoice(s) don't match. You can also concatenate the invoice number and amount to make a unique lookup key (assuming that invoice numbers and totals aren't repeated)

Good luck

Thanks, but we have already added the sum together, thats how we first noticed the discrepencies. Also, there is also the case that we have duplicate invoice numbers and totals are repeated. Over 40,000 accounts per invoice.

 

[MichaelA]

Basically, its just finding a formula, array or macro, that will find the discrepencies and highlight them or something.

I mean if it could do that, great.

I dont mind just having it do a vlookup and understand that their are multiple values.