Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reconciliation (2003)

    Hi
    I have a spreadsheet containing a sequence of approved payments in one group of cells, and a sequence of bank account deductions in another group of cells. They don

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Reconciliation (2003)

    Here is a possible approach, I don't have time now to do any coding on this and am not sure when I may find some time to think in more detail about it.

    You can use MATCH to check for single entry items (I assume you have accomplished this)

    To check for a the sum of 2 entries you can create an array of the sum payments with each of the the other payments). If any of the values match the items in the deductions, then you have a match of 2.

    You can take each of the sums that do not match, and you can create an array of each of the remaining values, and repeat looking in the list to match the sum of the 3 values
    You can continue this adding another item.

    The loop can stop when the smallest sum of the array is greater than the largest value in the deductions.

    Each loop would need to keep removing entries from the list as the matches are found. You can minimize some of the summing if you sort the payments first and eliminate sums when one of the values are obviously larger than the value testing.

    Hope this helps,

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reconciliation (2003)

    There just happens to be a post (and attached file) on this very subject at Dick Kusleika's Daily Dose of Excel website...

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reconciliation (2003)

    Thanks, dcardno, the info on that site solved my issue.

Posting Permissions

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