Register
Log In
Unanswered
Home
June 25, 2016
Offline
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
July 16, 2010
Offline
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
June 25, 2016
Offline
Thank you Mynda, that is very helpful.
Dave
June 25, 2016
Offline
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,
July 16, 2010
Offline
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
Dashboards
June 25, 2016
Offline
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
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: Ali Bokhari, ELINOR IGNACIO
1 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ali raza
Susan Booze
Rita Hrez
David Innes
preeti bhulabhai
Charles Klasek
Patrick Isedu
Emma Hayward
Vamseedhar G
NA NA
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
