Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Angry Data Matching problem.

    Help please .... I've been struggling with this issue for the last few days - very perplexing. I have been using a fairly comprehensive spreadsheet for many years to keep track of weekly rent payments from tenants. I import the Bank transactions each week & summarise them into a monthly table by income & expenditure type. This contains a running total column at the end showing the updated Bank Acct balance (or in this case Overdraft).
    I recently added a 'validation' procedure to check that the Bank balance from the import step summary agrees with the latest running total - which gets further & further down the page as the year progresses. This uses a MATCH command that simply looks for the correct Bank balance value in the running total column & returns an 'error' if it is not found. This seems to be working fine except in about 30% of the time the Error message comes up even though the correct balance does show in the running total column ie the Match command is not returning the correct result.
    I've check for formatting issues etc, and managed to fix some of these inconsistencies by adding a 'round(..)' prefix to the running total column cells.
    The import data comes from a csv file with correctly formatted values to two decimal places & there are no calculations being carried out except for some SUMIF totalling & '=a+b' type formulae.
    I can't see where any 'rounding issues' could be coming from, and wonder of there is something else I'm unaware of that could be the cause. The Copy/Paste Value step that's done manually at the start of each week to 'roll-over' the previous weeks closing balance does seem to cause some 'corruptions', but not in all cases. The attached file contains several worksheets & explanatory notes that give examples of the errors, along with the various stages I have been through with this. Test(4) is the final version of where I am at.
    I run this under Excel 2002, but get the same issues when using Excel 2010.

    Any comments, suggestion etc would be most welcome.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi John

    I'm not surprised you were struggling.
    This was indeed a perplexing issue!!

    On your sheet [Test(4)], you can do a quick check to see whether the result you are looking for is actually in column [O]:
    In cell [C24], enter this formula:
    =c21=o47
    ..the result says True, which means both cells are the same, so the Match function should have found it!

    You can also use this formula in [C24] to test the result:
    =COUNTIF($O$1:$O$58,C21)
    ..and the result should say 1, which means it did find one value that EXACTLY matched the contents of cell [C21] in the range.
    And yet the MATCH function couldn't find it!!!

    At first, I thought perhaps your worksheet was 'corrupted'.
    But I started with a new workbook in Excel2010, and manually entered (i.e. no copying) all the values, and then manually re-entered all the formulas, and still got the #N/A result for the MATCH function.

    So, curiously, if you do a direct compare with cells i.e. =c21=o47, you get a result True, which says they are the same, but if you try to match that value, it effectively says no it's not.

    Now this points to the Excel internal storage of these numeric values as being slightly different.
    So the solution is, as you discovered, to use the ROUND function in all of your column [O] formulas, which seems to satisfy the MATCH function.

    I have attached my version to show this.

    Weird!!!!

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2013-07-25 at 08:59. Reason: type

  3. #3
    Lounger
    Join Date
    Sep 2011
    Posts
    25
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Thanks for your quick response. Some relief for sure to know I'm not losing my marbles over seemingly 'simple' issues. However, being a glutton for punishment, I set up some further testing to see if there was any pattern or other connection between the error lines - fat chance of that! Check Sheet Test(5) for the results.

    One month had 9 errors out of 10 rows, & the other just 3 out of 9 rows. There were also several instances in which a fix to one error row, automatically 'fixed' one or more following error rows as well (without the need for the 'rounding' adjustment). Conversely on two occasions this also switched a correct row back to an error row in a couple of instances.

    See if you can identify anything of particular interest when you get a chance - or feel like a different challenge! No hurry, as I'll work on a week by week basis with the live data, making adjustments as I go.

    Thanks again for your assistance.

    John
    Attached Files Attached Files

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi John

    If you make sure you use the =ROUND( ) formula for ALL entries in your column [O], then I don't see any ERROR in column [S]

    zeddy

Posting Permissions

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