+ Reply to Thread
Results 1 to 10 of 10

Reconciliation

  1. #1
    Registered User
    Join Date
    03-14-2015
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    5

    Reconciliation

    I have 20k line items on my sheet which I need to match to reconcile my bank. There are multiple amounts of the same value. I need a formula that will create possible matches based on similar amounts that add up to zero (negative and positive)and highlight them. I have seen one thread with conditional formatting, but that didn't generate desired result and slowed down the excel program terribly. The data that needs matching is in column F of the attachment

    Regards
    Attached Files Attached Files

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Reconciliation

    The only items that offset in your example are +/- 644 in rows 44/45 and +/- 750 in rows 58/59. Is that what you expect?
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    03-14-2015
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    5

    Re: Reconciliation

    Yes but the file i am working on has 20k line items and there would be multiple items like that and they wont always add up to zero. The intention is to match as many as possible and leave the unmatched ones unhighlighted. Will it be easier if i uploaded the original file?

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Reconciliation

    You are looking for 1:1 matches, correct? If so, yes, please upload the file.

  5. #5
    Registered User
    Join Date
    03-14-2015
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    5

    Re: Reconciliation

    the file exceeds the allowable size limit of 1000kb, any other way I can upload the file here?

    Regards

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Reconciliation

    Put it on box.net or dropbox and post a link.

  7. #7
    Registered User
    Join Date
    03-14-2015
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    5

    Re: Reconciliation


  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Reconciliation

    This takes several minutes to run. You can watch progress in the status bar.

    It cancels about half of the 42K transactions. Results appear to the right of the last used column (i.e., with a blank in row 1).

    Please Login or Register  to view this content.
    Last edited by shg; 03-17-2015 at 01:57 PM.

  9. #9
    Registered User
    Join Date
    03-14-2015
    Location
    Dubai
    MS-Off Ver
    2007
    Posts
    5

    Re: Reconciliation

    Thanks a Ton! That works really well. Is it possible that the macro matches items with nearest date and leaves the most recent item as unmatched?

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Reconciliation

    It does match them to the nearest date, because it traverses the list from top to bottom. If you want it done in opposite order, sort by date descending.

+ 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. Reconciliation
    By Shihab in forum Excel General
    Replies: 2
    Last Post: 01-12-2014, 12:21 PM
  2. Inventory Reconciliation
    By CKU in forum Excel General
    Replies: 10
    Last Post: 07-08-2013, 12:54 PM
  3. Reconciliation
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-22-2008, 05:05 AM
  4. Reconciliation
    By cmt_london in forum Excel General
    Replies: 1
    Last Post: 02-19-2008, 09:14 AM
  5. Reconciliation
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2005, 11:01 AM

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