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

Identifying contra entries (debits and credits) in a workbook

abdulkarim1983

New Member
Hi


I have a workbook that records purchase invoices from suppliers.


There are 2 columns: Column A = Supplier and Column B = Amount


Sometimes an item is purchased but subsequently refunded. As a result we have a purchase (for say $500) and a credit (of -$500) which net to zero. Such items are called 'contras' as they net to zero.


Now what I need to do is identify all the contra entries in the spreadsheet. However, the condition is that to numbers which net to zero can only be a contra if they are from the same supplier.


Can anyone please help me find a way to do this?


e.g. The contra items below are lines 1&4 and 5&6 (2&3 don't contra as they are from different suppliers).


JAMES MORGAN -2,100.00

JAMES MORGAN 5,678.00

THINK PUBLISHING LTD -5,678.00

JAMES MORGAN 2,100.00

EXPERIAN LTD -2,176.47

EXPERIAN LTD 2,176.47


Your help is very much appreciated.


Thanks
 

Sajan

Excel Ninja
Hello Abdul Karim,

Assuming that your suppliers are in column A, starting in cell A1, and are named "Suppliers"

Assuming that the amounts are in column B, starting in cell B1, and are named "Amounts"


Put the following formula in cell C1:

=IFERROR(MATCH(1, (Suppliers=A1)*(Amounts=-B1), 0), "")


enter with Ctrl + Shift + Enter, instead of Enter


Copy down to additional rows


The formula should return the rows where a contra matching entry was found.


If your data starts on some other row than 1, remember to increment the MATCH() results by that amount.


For your sample data, I got the following output:

[pre]
Code:
James Morgan	     -2100	     4
James Morgan	     5678
Think Publishing     -5678
James Morgan	     2100	     1
Experian	     -2176.47	     6
Experian	     2176.47	     5
[/pre]
Cheers,

Sajan.
 

Sajan

Excel Ninja
Hello,

Given that you are using Excel 2007...


What do you get when you try the above approach?
 

webmax

Member
hi use link after theat you can sort to reconcile.

https://www.dropbox.com/s/hdsktpgpbzwtt1m/formula.xls
 

ManivannanG

New Member
Hi ,

I have Same Scenario of Contra entry capturing in my Spread sheet. Ca you please assist.
i tried with Above function but may be i could assume the data correctly and relate with my Data Sheet.
is there any function you could suggest.


Customer name is in Column T and Amounts are in Column I
and i need to return either contra or the cell number on the match.

Thanks in advance.
 
Top