+ Reply to Thread
Results 1 to 10 of 10

Error checking: make sure that ID values increase by one digit at a time

  1. #1
    Registered User
    Join Date
    06-28-2011
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    29

    Question Error checking: make sure that ID values increase by one digit at a time

    Hello,

    I am trying to code a solution for a problem I have.

    In column A of my spreadsheets I have blocks ID strings that are all made up of two letters and five digits (ex: AB12345). Each ID appears at least on one row (but usually more), then there is a blank and the next ID starts. The attached file shows full examples.

    Correct:
    AB12345
    AB12345
    AB12345
    BLANK
    AB12346
    BLANK
    AB12347
    AB12347

    Incorrect:
    AB12345
    AB12345
    AB12345
    BLANK
    AB12344 (this should be pointed out as an error)
    BLANK
    BLANK (double blanks should also be pointed out)
    AB12347
    AB12347

    I want to make sure that the ID numbers count up properly, so I want the macro to point out where the numbers skip (ex from AB12345 to AB12347).

    Each ID should be followed by one that is one number larger (so after AB12345 you have AB12346).

    Basically the code should:
    - For all cells with data in column A (starting with A2)
    - While the current cell is not a blank make sure that all ID numbers are the same until you hit a blank (if not output the row where the error occurs)
    - When you hit a blank cell make sure that the cell under the blank is one number bigger than the ID above the blank (if not output the row where the error occurs). Also, if there are two blanks in a row the macro should point it out too.

    That is all, it sounds easy, but I made a mess when trying to code it, and I am sure that there must be an easier way to do what I am trying to do.

    Please Login or Register  to view this content.
    Any help would be great. Thanks everyone!
    Attached Files Attached Files
    Last edited by vzc8; 09-21-2011 at 12:50 PM. Reason: adding info

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Error checking: make sure that ID values increase by one digit at a time

    Try this. What should happen if both errors occur in the same row or can't that happen?
    Please Login or Register  to view this content.
    EDIT: actually may not be quite right. If all the cells in one block are not the same it will flag all as different. Will there only ever be one exception? If not, what should happen?
    Last edited by StephenR; 09-20-2011 at 11:35 AM.

  3. #3
    Registered User
    Join Date
    06-28-2011
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Error checking: make sure that ID values increase by one digit at a time

    StephenR, thanks so much for the quick reply.

    This ID data only exists in column A. The example file has text on multiple columns just to show different possibilities.

    Since the data only exists in column A, there will only ever be one error per row.

    I tried your macro and it works perfectly for pointing out the mixed blocks. It is fine to flag all the block as different as your macro does. However I do not think that it is catching double gaps. Your code is a bit beyond me so I am having a hard time trying to figure out how to do that.

    AB12344 (points out this and marks the whole block)
    AB12345
    AB12345
    BLANK
    AB12346
    AB12340 (and this, marks whole block too)
    AB12346
    BLANK (but not this)
    BLANK
    AB12347
    BLANK
    AB12340 (catches this too)
    BLANK
    AB12349 (and marks this as incorrect, but not the rest of the block)
    AB12349
    AB12349

    Highlighting will be best for me, so I edited the code a bit.

    Please Login or Register  to view this content.
    Thanks again for your help.

  4. #4
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Error checking: make sure that ID values increase by one digit at a time

    OK, I missed the 2 blank rows thing but easy to fix. In the last block what should happen because they are not all the same so all four are flagged as different, but also the 12349 is not in sequence after 12340 so should that be skipped also/instead of?

  5. #5
    Registered User
    Join Date
    06-28-2011
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Error checking: make sure that ID values increase by one digit at a time

    Thanks again StephenR!

    In the example below it would be best if the whole last 12349 block was highlighted (so all four in this case).

    Better more highlighting than less, it will make it easier to spot the mistakes. The files I will be working with will be very large and hopefully have very few of these mistakes.

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Error checking: make sure that ID values increase by one digit at a time

    I think that's what happens now, but my point was that the first cell breaks both rules "skipped" and "different".

  7. #7
    Registered User
    Join Date
    06-28-2011
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Error checking: make sure that ID values increase by one digit at a time

    Hi StephenR,

    Yes, 12349 would be both skipped and different, so it should be highlighted. Being skipped is not that important, what really matters is that it is not in the correct order. However, keeping track of blank lines is important to catching two in a row.

    right now the code works well, but not perfectly:

    AB12344

    AB12345
    AB12347 (highlights this block)
    AB12345

    AB12346
    AB12346

    AB12346 (highlights this row)

    AB12347

    AB12348

    (misses this, two blanks in a row is an error)
    AB12349
    AB12349
    AB12349

    Let me know if you have any ideas on how to fix that.

    Thanks again for the help!

  8. #8
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Error checking: make sure that ID values increase by one digit at a time

    This should catch more than one blank row:
    Please Login or Register  to view this content.
    Last edited by StephenR; 09-21-2011 at 05:20 AM.

  9. #9
    Registered User
    Join Date
    06-28-2011
    Location
    Spain
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Error checking: make sure that ID values increase by one digit at a time

    StephenR,

    I have tested this and the code seems to work perfectly. Your version is very concise.

    I will take some time to study it, there is much I do not yet understand as I am still learning.

    Thanks so much for helping me out with this.

  10. #10
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Error checking: make sure that ID values increase by one digit at a time

    My pleasure.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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