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

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

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.

#### shafiq-ur-rehman

##### New Member
hello

i tried the above said solution but it didn't work in excel 2007..

any other remedy by the members?

regards

shaqs

#### Sajan

##### Excel Ninja
Hello,

Given that you are using Excel 2007...

What do you get when you try the above approach?

#### webmax

##### Member
hi

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

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