• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Attempting to detect recycled Mobile transaction codes

shili12

Member
This is the scenario, see illustration below and file attached along with desired output :-
Note i have large file with over 80,000 lines.
this is extracted after fuzzy logic, as cashiers may err by placing 1 as I, S as 5, O as 0, or omitting a digit or so.
on the ERP side the transactions are separated , but in mobile statement they are aggregated.
The first example is known to be valid.
ie same mobile reference ODH5JJJSW3 1350+2000=3350(aggregate) on the right hand as mobile statement.
the trick here is to see which amounts on ERP total to aggregate/sum to mobile , and those which dont , to be highlighted.
This will mean cashier is using recycled code to circumvent the transaction causing a shortfall!
Any method will do, formula, VBA, conditional formating, power query, etc



72866
 

Attachments

p45cal

Well-Known Member
In cell M4 (or any spare column in the same row):
=SUMIF($F$4:$F$13,$F4,$E$4:$E$13)<>$H4
and copy down. Will show TRUE if the aggregate doesn't agree. Can be used as a conditional format for the whole table.
 

shili12

Member
Perfect, works like a charm, and also with conditional formatting too, if the formula is placed there.
Next stage is analyse 80,000 or so lines, this will me save me some ample time.
72883
 
Top