+ Reply to Thread
Results 1 to 7 of 7

Reconciling macro

  1. #1
    Registered User
    Join Date
    01-02-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Reconciling macro

    Hello talented gurus,

    While I have searched the internet, this forum and made various attempts to piece together a macro, I have failed (over and over) and resorted to writing in formulas... which aren't quite doing the job either. I'm hoping with your assistance I can get this file working much better with macros and without or with few formulas.

    Objective - reconcile a single worksheet of transactions, mark as reconciled, then move reconciled items to new worksheet

    Issues I have encountered
    - The Location, Type and $ are conditions for each line when reconciling (hence the "My attempt" tab where I used unique values), so that only items for those locations and payments types are clearing against each other.
    - The file I am working with has over 30,000 lines, so duplicate values often occur

    Writing it out like this, it seems fairly simple, and I feel like I should be able to accomplish it on my own, but sadly I have not.

    Many thanks in advance.
    Recon with formulas v1.2.xlsx

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,564

    Re: Reconciling macro

    Well, there were some problems with data, which could made your attempts less effective :-P.
    First - you were inconsistant with formulas. In column D there were sometimes
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    , sometimes =
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Second - unfortunately, due to floating point accuracy some numbers did not match exactly - read for instance here: http://support.microsoft.com/kb/78113
    After correcting first (not tested if it really influenced the matching) and taking into account second problem, following macro:
    Please Login or Register  to view this content.
    Seems to work fine and quick enough on test data. Of course on larger datasets it will take some time to complete (writing directly into spreadsheet is time-consuming process). I'd expect something like "go for one coffe" ;-) for 30000 rows of mostly matching data.
    See attached workbook. Note that it also takes into account date (sequence of events in time) - see for instance rows 57-60 of output sheet.

    Best Regards,
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,564

    Re: Reconciling macro

    At the moment it's not marking, but moving matching data - of course one can easily either adopt current code, or just after the process mark all in output sheet as reconciled, and copy back to data sheet.

  4. #4
    Registered User
    Join Date
    01-02-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Reconciling macro

    Quote Originally Posted by Kaper View Post
    At the moment it's not marking, but moving matching data - of course one can easily either adopt current code, or just after the process mark all in output sheet as reconciled, and copy back to data sheet.
    What a brilliant way to display how each set of items is clearing. This feature I really like.

    I applied the code, and I have encountered an issue...
    It appears items clear as absolute values, rather than + clearing against - (as with debit/credit). I neglected to clarify that Source "GL" should clear against "Report". The prior comments are more or less 1 in the same, as the GL is all the deposits.
    For reference, I added a "Proof" column to the "Reconciled". (Sorry!)

    Thank you for the help!

    Recon with macro v1.4.xlsm

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,564

    Re: Reconciling macro

    Ooops, shame on me, of course abs of sum shall be tested (it worked fine, not removing two positive values, but on two negative indeed, there was a bug).
    So the propper code is:
    Please Login or Register  to view this content.
    Last edited by Kaper; 01-05-2015 at 10:12 AM.

  6. #6
    Registered User
    Join Date
    01-02-2015
    Location
    California
    MS-Off Ver
    2010
    Posts
    3

    Re: Reconciling macro

    Works beautifully! Thank you again for the help, especially the thoughtful code for the Reconciliation tab. Super, super helpful.
    Hope you have a Happy New Year.

  7. #7
    Registered User
    Join Date
    06-26-2015
    Location
    Winnipeg
    MS-Off Ver
    Office 365
    Posts
    17

    Re: Reconciling macro

    Hi there,

    I need to do the same kind of reconciliation without taking into account posting date etc. just the amount. i tried to modify the code to do this but I have been unsuccessful. Any help would be greatly appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for Reconciling Data
    By JCOOP23 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2014, 02:41 PM
  2. Stock Cash Account Free Riding Violation Reconciling Macro
    By swade730 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-02-2013, 06:56 PM
  3. Reconciling 2 Spreadsheets
    By xuetengwang in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2013, 08:12 PM
  4. Macro for Reconciling Intercompany GL Activity
    By amartin575 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-05-2012, 06:35 PM
  5. Reconciling Two Lists
    By DAlter in forum Excel General
    Replies: 4
    Last Post: 07-27-2009, 12:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1