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?
--
Regards
Roger Govier
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> On 2 Jun, 10:23, "Roger Govier" <r...@technologyNOSPAM4u.co.uk> wrote:
>> 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.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>> <amorrison2...@googlemail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> > 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- Hide quoted text -
>>
>> - Show quoted text -
>
>
|
Similar Threads
