Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom function for matching bad data (2000)

    One for the gurus:

    I am trying to match a list of tag numbers from physical inventory against a list of tag numbers that I know are correct. At least I assume so since they came in a spreadsheet from Dell.

    The problem is that the tag number is full of zero and O's; ones and ells; fives and S's, and on.

    It gets worse as a typical tag number is 5-7 characters and could have multiple typos.

    I tried writing a custom function that generated all the different ways the data could be entered depending on how many of the problem characters there are.

    Example.

    1S2XF -> LS2XF or L52XF or 152XF

    List of incorrect characters that might be switched.

    0 and O
    1 and l (L)
    2 and Z
    3 and B and 8
    5 and S
    6 and G
    7 and T
    8 and B or 3
    9 and g

    I want a simple lookup that picks tries the different combinations and then does a lookup to see if it is a hit.

    I tried nested if statements, and it gets ugly.

    I tried the case statement, but it seems to be too limiting, unless I try nesting them as well.

    Please, please, please help me get on the right track.

    Should I stick with the custom function, or try squeezing it of nesting existing functions?

    Also, does anyone have a similar code that I can adapt.

    Thanks in advance.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom function for matching bad data (2000)

    Can you upload the workbook that contains the valid tag numbers and at least some of the bad one that can be used for testing?
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom function for matching bad data (2000)

    See the attached workbook.

    It contains a custom function ChangeTag that is used to match the tags.

    As you'll see, some of the tags are still not matched.
    One is caused by J / G. You could add this to the function.
    The other is caused by transposed characters: X4 vs 4X. To handle transpositions is trickier.
    Attached Files Attached Files

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom function for matching bad data (2000)

    I have attached a sample set of both the masterlist that includes examples of tags that have letters that get mixed. Most of the tag numbers are 5 characters, but some are 6. There is no reason to believe Dell won't go to 7.

    I added a tab that shows tag numbers and physical inventory date. I typed these tag numbers with intentional mistakes in the form they appear, except the first one that is correct on both lists. I only made one with two typos.

    I also through in the custom functions I was playing with to show you where I was headed.

    One after thought. I see that it is possible to have multiple hits since the full master lookup is 3,500 rows. since I am doing it as a QC step, I would not mind if it returned all the returned all the hits in one cell or adjacent cells. I will have to manually review the matches with multiple hits anyway.

    Thanks again for the help.
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom function for matching bad data (2000)

    thanks for looking at it.

    I was getting similar results. I was reluctant to put all 3,500 tag numbers in a post but my sample data did not allow for 2 typos in the same line, which will happen as well. If both a 0 and a 1 are typed wrong, there could be 2 or more tag numbers that match.

    I was thinking about doing a lookup inside the loop to see if it gets a hit. If it does, save the tag number and run through the loop again to see if any other combiniations might work.

    I had not thought about the transposition problem - I guess I need to start playing with that as well.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom function for matching bad data (2000)

    If you have MS Access, you can

    Import the tables into Access,
    Put the custom function in a module,
    Build queries with the modified tag as a calculated field,
    Match on that field, and
    Use the Find Duplicates Wizard and the Find Unmatched Wizard to ... uh, well, you guess.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom function for matching bad data (2000)

    See if the attached gets close to what you want.
    Attached Files Attached Files
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Mar 2002
    Location
    All Over, CA, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom function for matching bad data (2000)

    Thanks, I will work on refining the two that did not match. The X4 - 4X transposition was my own typo. I am sure it will happen in the data. What you gave me solved everything I asked for. thanks again.

Posting Permissions

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