Formula(ae) for duplicates

**georgeparker24** · 10-31-2012, 08:25 AM

Hello all,
I am an accountant, and have the pleasure of going through various bank statements and bags full of receipts when computing tax returns.
I want a formula to find duplicates, so when I’ve typed up all the transactions, from say 5 bank statements and two full bags of cash receipts, I’m not double counting.
For example, I may have a cash receipt from Sainsbury for say £3.96, as well as bank transaction for the same amount. If I could use this formula, I could then discount the receipt as I know it’s gone through the bank.
Similarly, Vice a versa, if I have a receipt for £3.96 and I can’t see it through the bank statement, I know the guy has paid for it with cash.
I’m aware I can use the find button, but that doesn’t really help me much, I need something that can find all duplicates through a whole worksheet.
Thank you
George

**dilipandey** · 10-31-2012, 08:44 AM

Hi George,

Welcome to the forum.
Suggest you to share an sample workbook... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**katiespirations** · 10-31-2012, 08:51 AM

do you have you data in one sheet? or there is one for bank transactions and the other one for receipts?
in both way you can use COUNTIF function
this is gonna tell you how many times the exact amount is in the worksheet
e.g.
you have the amounts in column E, so put this formula in any other column
=COUNTIF(E2,E:E)
so when you get value 1 it means the amount is just on the receipt or on the bank statement

not sure you want this but you can try

**abousetta** · 10-31-2012, 08:54 AM

Also you can use conditional formatting to highlight duplicates. There are a million and one ways to do this, but for us to be able to help we need to see a dummy copy of the workbook you intend on using. Dummy data is fine, just keep the structure the same and would be great to see a before and after or what you would like the results to look like.

abousetta