Eliminating contra entries « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Eliminating contra entries

(24 posts) (5 voices)

Started 1 year ago by purvi
Latest reply from Faseeh

Tags:

purvi

Member
Joined: Feb '12
Posts: 10

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

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

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

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

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??

Posted 1 year ago #
SirJB7

Excel Ninja
Joined: Dec '11
Posts: 4,515

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!

Posted 1 year ago #
fred

Member
Joined: Nov '10
Posts: 516

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.

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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.

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

Hi ,

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

Narayan

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

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

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

Hi ,

Thanks.

Narayan

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

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

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

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

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

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

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

send it to me on exceltest4@gmail.com

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

@ NARAYANK991

Its a great formula, a really good one!!

@ Purvi,
Sorry, my formula is still faulty.. :(

Faseeh

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

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!

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

@ 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???

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

@ Faseeh - in such cases either 1 and 2 or 2 and 3 should be marked as contra.....not all three.

Posted 1 year ago #
purvi

Member
Joined: Feb '12
Posts: 10

NarayanK991's solution is perfect!!!

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

Posted 1 year ago #
NARAYANK991

Excel Ninja
Joined: Oct '11
Posts: 4,085

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

Posted 1 year ago #
Faseeh

Excel Ninja
Joined: Jan '12
Posts: 1,591

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

Posted 1 year ago #