Hello -
I have been using a tip from a blog post on 'bank reconciliation' for quite awhile, and it has been very helpful. This post recommends using the 'IF' and 'COUNTIF' functions. This has worked well for me when I have pulled only data for a single day's activity, but I need to try to use this for large runs of data, up to 30 days at a time, without doing each day separately. I need to still match and remove charges and credits (or debits and credits), but I need to match them for each day independently, so that say a $10 charge on the 1st won't match up to a $10 credit on the 2nd, for example. Given that I am only a moderate Excel user, this is likely a simple solution (I hope..). Attached is a sample of the type of transactions involved.
Thanks in advance for any help with this.
D
Hi,
Add the date to the text string to create a unique text string that is date specific:
=IF(D2<0,-D2&"-"&COUNTIF(D$2:D2,D2)&A2,D2&"-"&COUNTIF(D$2:D2,D2)&A2)
See file attached.
Mynda
Thank you Mynda, that is very helpful.
Dave
DBuck said
Thank you Mynda, that is very helpful.Dave
Dear Mynda,
If different column,i mean amount in Dr column nd Cr Column,how can we trace the same amount nd different sign?
Regards,
Hi Rath,
Rather than over complicate the formula I'd just create a single column. Convert Credits to negative values so you can keep track of which amounts are debits vs credits. Then use the above technique, or if you don't need the extra date string then use the technique described here:
https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula
Mynda
Hi Mynda
If I have a unique key e.g. cheque no., I would prefer using a Pivot Table. This allows me to see all matched and unmatched transactions.
I can then copy those unmatched (partially match) transactions to the following month.
Sunny Kow
Mynda Treacy said
Hi Rath,Rather than over complicate the formula I'd just create a single column. Convert Credits to negative values so you can keep track of which amounts are debits vs credits. Then use the above technique, or if you don't need the extra date string then use the technique described here:
https://www.myonlinetraininghub.com/excel-bank-reconciliation-formula
Mynda
But your case debit nd credit in the same column.if it is seperately column.how can you trace,dear please show how
