Formula to match two conditions

[Jai]

Hi,

I am trying to set up a vlookup formula to match two database. Following is
the example :

Sheet A List :
Col.A Col.B
Vendor amount
40694 2,154.51
40986 150.00
40986 250.00
125855 1,454.37
125855 2,272.96
166559 2,458.10
166559 16.81
166559 62.54
173204 4,611.53
173430 571.34

Sheet B List :
Col. A Col.B Col. C Col.D Diff (Col.B-Col.D)
40694 2,154.51 40694 2154.51 0
40986 150.00 40986 150.00 0
40986 250.00 40986 150.00 100
125855 1,454.37 125855 1454.37 0
125855 2,272.96 125855 1454.37 818.59
166559 2,458.10 166559 2458.10 0

Sheet A has all data for all vendors and amounts receivables. In sheet B,
all the data represents received amounts.
Please help me with following :
I need to match Sheet B with Sheet A for all the amounts received so Column
C and D shows the matched value and E column shows the difference in the
amounts.

In Sheet A, either I need to delete all the rows which has been matched with
Sheet B or need to set up a formula for all the outstanding amounts to be
received.

I am trying the Vlookup function and its returning to first match so there
are too many differences. As the lists are huge everymonth. I need to come up
with some formula or macro.

Thanks
Jai

 

[Pete_UK]

Do columns A and B of SheetB come from somewhere else then? Like your
Invoicing system, but SheetA is for payments received? How can you
ensure that the two lists are in the correct order? Say for example
that the 2nd and 3rd items were reversed in your payments list - if
you matched on the first one within SheetB then you would have a +ve
difference of 100, but the next one would be -100. I could give you a
method based on this, but as can be seen it wouldn't necessarily make
the correct matches.

I suppose a safer method would be to introduce a new column C in
SheetA and put this formula there:

C2: =A2&TEXT(B2,"0.00")

and copy this down.

Then in SheetB you could enter this formula in C2:

=IF(ISNA(MATCH(A2&TEXT(B2,"0.00"),'Sheet A'!C$2:C$10000,0)),"not
present","matched")

and copy this down. This doesn't address your problem exactly, but
perhaps you can see how to adapt it.

Hope this helps.

Pete

 

[Jai]

Thanks for your reply. You are right, there are some rows which are either
ins/out or -ve amounts.
In sheet B Col. A & B are coming from Daily deposits. In Sheet A col. A & B
are coming from different source. Also the formula suggested by you works to
get outstanding list. I copied the formula in Sheet A and then filtered for
"not present". If its in/out then I will delete rows manually.
Is there any way to do a formula for Sheet B that after match, matched value
of column A comes in one column and matched value of Column B comes in next
column and I can see the differences in next column. As I alreay stated I am
using Vlookups but not working for multiple entries for one vendor number.

Please response.

Thanks
Jai