Complicated Macro to Match - Not for the faint hearted!

[amorrison2006]

Hello

I have a spreadsheet which I have to reconcile between our customer
orders in the system and our bank account.

I dont know if this requires a complicated if statement but here is
the criteria of what I need,


1. I have a list of transactions from our system and the bank account
in the same sheet.

Column A identifies between the system and the bank accounting using
the characters SYS and BACC.

Column B contains the type of order.

Column C contains the order number which should also match the bank
account.

Column E contains the amount from the bank account and the amount from
the order.

IF the following criteria are met then do the below.

1. Column B match for both rows then column E matches for both rows
then column B. Column A rows much have only one row that matches so
the criteria must be one for sys and one for bacc.

THe above criteria is the only way I think we can match the entries
off completely.

One the criteria has been met I want both rows copied to a new sheet
called "Matched" so the data sheet just leaves entries which have not
been matched up.

Here is some sample data,

ColA ColB ColC
ColD ColE
SYS S A12345LP
DD 100.10
BACC S A12345LP
DD -100.10
SYS D A12345LP
DD 99.99
BACC D BA12345LP
DD 99.99
BACC E 90222EAP
DD 120.00
SYS E 444555BAP
DD 12.00
SYS S B12345AA
DD 89.97
BACC S B12345AA
DD 89.97

If the criteria I set is applied then I would expect to see copied
into the new worksheet "Matched" the below transactions which have
been matched off.

There must be an entry for both SYS and BACC regardless as to whether
the amounts are positive or negative as long as they match to the
penny.


ColA ColB ColC
ColD ColE
SYS S A12345LP
DD 100.10
BACC S A12345LP
DD -100.10
SYS S B12345AA
DD 89.97
BACC S B12345AA
DD 89.97

Me even getting the thinking in my head correct was
difficult.........I really hope someone can help with this,

Thanks so much,

Your all really amazing at these things and I do appreciate all your
help.

Andrea

 

[Roger Govier]

Hi Andrea

If I have understood you correctly, then enter in F2
=IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)*
($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched",""))

This will produce a Matched pair for the first 2 lines, but not for the
last 2 as their sign is the same. Shouldn't one of them be negative?
If you want a match regardless of sign, then
=IF(E2="","",IF(OR(SUMPRODUCT(($C$4:$C$9=C2)*
($B$4:$B$9=B2)*$E$4:$E$9)=0,
SUMPRODUCT(($C$4:$C$9=C2)*
($B$4:$B$9=B2)*$E$4:$E$9)=2*E2),"Matched",""))

Copy down column F
Apply Data>Filter>Autofilter and used the dropdown on column F to select
Matched.
Mark the filtered rows by selecting their row numbers, Copy>paste to new
sheet.
Mark the filtered rows again on Source sheet, Delete.

 

[amorrison2006]

Does no one know how to do this in a macro to save me the risk of
making a mistake?

There is a risk in doing these long excel formulas as I don't really
understand where as if I have a macro that can be variable then that
always works best for me ,

Please let me know,

Thanks

Andrea

 

[Roger Govier]

Hi Andrea

The formulae are not complicated.
You didn't respond as to whether two identical sums of the same sign are
to be matched or not.
=IF(E2="","",IF(SUMPRODUCT(($C$2:$C$9=C2)*
($B$2:$B$9=B2)*$E$2:$E$9)=0,"Matched",""))

Breaking this down
IF(E2="","", this is to prevent the formula writing the word matched
against blank rows further down your sheet, where there are no values in
column E. So if there is nothing in column E, enter a null in column F

The SUMPRODUCT() part is adding together all values from column E, where
rows have identical values in cells in column C (regardless of whether
they are adjacent)
and
rows have identical values in cells in column B.(regardless of whether
they are adjacent)

If the sum of these values is Zero, then the column F shows the word
"matched" against the relevant lines, because all of the rows are equal
and opposite. i.e 100.10 + -100.10 = 0

In the second case, the OR function is merely adding to the above, the
word "matched" also, if the SUM is not equal to Zero, but is equal to
twice the value found in column E i.e. 89.97*2 matches 89.97+89.97

Did you try it?