• 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.

Eliminating contra entries

P

purvi

New Member
Hi, I have an excel file containing a list of accounting entries running into thousands of lines. I need to mark as "contra" all rows where the transaction amount is debit and credit.


Eg:

Row Account No Desc Amount

1 1234 Rent 500

2 8910 Cash -500

3 4560 A's costs 45

4 5100 C's salary 100

5 60000 D's cost 100

6 78910 Cash -100

7 11000 Cash -100


In the above list, I need to mark rows 1,2 4,5,6,7 as "contra"


Can anyone please help with a quick way to do the same?


Thanks in advance
 
Hi ,


In the data you have given , rows 1 and 2 have amounts of opposite sign , whereas the other rows don't ; what is the rule for deciding whether two rows are to be marked "contra" ?


Will the two rows be one immediately below the other ?


Narayan
 
Hi purvi,


What i have observed that you want to mark:


Rent When it is +ive,

Cash When it is -ive,

Salary When it is +ive,


But What about Cost??
 
Hi, purvi!

For rows 1 and 2, we can suppose that even there are no other linking elements if we find two consecutive positive and negative numbers they're "contra", but for upon what criteria should we attach "contra" to rows 4 thru 7?

Regards!
 
if you want to find only the difference in amount, then just sum the total.


In real life it's not that simple in accounting. you could have entries like +500 (A) but -150 (A), -25.5(A), -82.45(A), -242.05(A) to cancel out. but you may have an identical entry of -25.5(B) somewhere else. if you apply -25.5(B) to +500(A) you would leave with -25.5 (A) as the contra. You just can't go to your boss and says he's missing an entry of +25.5(A). That'd be double entry.


you want to find out which accounting object doesn't have a missing entry. not just any entry.
 
Hi....what I need to do is find and eliminate equal and opposite transactions from the list.....so in my example above - row 1 and 2, row 4 and 6 and row 5 and 7 are equal and opposite....I need to mark these as contra and then look at only the unique entries ie the ones which do not have an offset.


At the moment what we usually do it, add a column, use ABS to get absolute values, then sort on ABS and then manually mark as contra by visual comparison...I was wondering if there is a smarter way to do it.
 
Hi ,


Can you please clarify with reference to my first post ? How do you determine two entries are equal and opposite ?


Narayan
 
@ Nayayank991 - You can only determine that by looking at the transaction amounts - if the amount is the same and one has a + and another - then the two entries are equal and opposite . To answer your second question - no the two rows need not be necessaily consequite - if that was the case then visual elimination would be very very easy.
 
Hi ,


Thanks for clarifying. Let me summarise :


1. For each amount , if there is an equal and opposite amount somewhere in the column , that particular amount is to be marked as contra.


2. The two amounts , one positive and the other negative , need to be set off against each other.


3. Further matching of amounts will exclude the amounts which have already marked as "contra".


Narayan
 
Hi...Points 2 and 3 - yes!


Point 1 - no, not necessary that each amount will have an offset - the purpose of this exercise is to identify the ones that do and mark them as contra. But there could be amounts in the list that do not have an offset
 
Hi purvi,


so you simply want to compare two consective entries if they are equal but opposite in signs tryout this:


=if(ABS(OFFSET(B2,0,-1))=ABS(OFFSET(B2,-1,-1)),"CONTRA","")


ur data is located in COL.A (A2 onward) and enter this formula in B2 n drag.... Hope it works as ur are expecting.....


Faseeh
 
@ Faseeh - tried it but the formula only marks the second entry of a pair as contra not both the lines :(
 
Hi ,


Can you access Skydrive or GoogleDocs ? I'd like to upload a workbook , which you can then check with more data.


Narayan


EDIT :


Check out the following link :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21165
 
Hi purvi,


You were right, but i though it would be sufficient to highlight only one row, try out this one it is marking both the rows:


=IF(ISBLANK(A2)=FALSE,IF(OR(ABS(A2)=ABS(A1),ABS(A2)=ABS(A3))=TRUE,"CONTRA",""),"")


Your data is located in colA (A2 onward) and you are entering this formula in B2, enter and drag. Hope it works (its working here)


Faseeh
 
hi Faseeh, tried the revised formula....it works fine except for cases where I have five lines with the same amount (2 debits and 3 credits or vice versa)...in that case instead of marking four lines as contra - it marks all five....
 
@ NARAYANK991


Its a great formula, a really good one!!


@ Purvi,

Sorry, my formula is still faulty.. :(


Faseeh
 
A possible solution I have found is :


I add an extra column, use ABS, then I sort on ABS and then use the following :


On row 1 : If (A2= -A3, "contra",0)

On the rest: If (B2 = "contra", "contra1", if (A3= - A4, "contra",0))


The above works except for cases where I have 2 debits followed by 2 credits - in that case it picks and marks the second and third row but misses out on the first and last which are also offsetting!
 
@ Purvi,


How do u handle this situation if three consective entries are equal with diff signs like this:


100

-100

100


What should be rule here? Should we write 'contra' against all the three or against 1-2 or 2-3???
 
NarayanK991's solution is perfect!!!


Narayan : Can you please provide the link to the workbook with the formulae? Thanks a ton!
 
Hi ,


I assume you mean the worksheet with your data in it ? I hope there is nothing confidential in it !


The link is :


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21166


However , I am still not very confident about its correctness , so I'd advise its use with caution !


Narayan
 
Hi NARAYANK991,


Why the file is taking so long to calculate formula? Well I agree with you that its result are some what strange.


Faseeh
 
You must log in or register to reply here.
Back
Top