+ Reply to Thread
Results 1 to 8 of 8

Identifying almost similar cells

  1. #1
    Registered User
    Join Date
    09-13-2011
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    7

    Identifying almost similar cells

    Hi all,

    I have an excel sheet with two sets of data (on separate sheets) which are used in two separate systems (System A and B). In the near future these two systems are going the communicate with each other via an interface. Communication is only possible if the data matches exactly. Unfortunately there where some errors made during the data migration. E.g. in system A a data point is named 1234-AA-0001 and in system B its called 1234-AA-001.

    I want to identify these errors. I have already tried standard excel function as vlookup however this doesn't work because it doesn't remove the matched cells in the range.

    What I want is a function that moves a row from sheet 1 (system A) to a new sheet if:
    - the first 6 characters match
    - and the last 3 characters match
    - and the total number of characters is not equal

    I have been trying to solve this for almost a week now. I hope some of you can help me.

    Thank you in advance.

    Best regards

    Maarten

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Identifying almost similar cells

    Hello VBnoob,

    Welcome to the Forum!

    If "Sheet1" is for system "A", where is system "B" located?
    Can you post the your workbook for testing and macro development?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    09-13-2011
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    7

    Re: Identifying almost similar cells

    Hi Leith,

    Thanks for the quick response. Attached is is a sample of the data. Please note that both data sets contain some data that is totally different, this is not a problem, its about finding data that is a little bit different.

    Again thanks for your help!
    Attached Files Attached Files

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Identifying almost similar cells

    Hello Maarten,

    Thanks for posting the workbook. I have 2 questions about the matching characters. First, does case matter in the comparison? Second, are the hyphens considered to be part of the matching characters?

  5. #5
    Registered User
    Join Date
    09-13-2011
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    7

    Re: Identifying almost similar cells

    Hi Leith,

    Yes, hyphens do matter. The interface will only work with exact matches. When a data point does not exist in system B it will be generated automatically in system B. The two data sets are just names. In the systems a lot more information is connetcetd to them.

    I don't understand what you mean with case.

    Hopes this answer makes things a little more clear?
    Last edited by VBnoob; 09-13-2011 at 03:48 PM.

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Identifying almost similar cells

    Hello VBnoob,

    You answered the question about case with exact matches. Sometimes when comparing alpha characters, it doesn't matter if the letters are upper case or lowercase. That is an "A" is the same as "a" when not doing a case sensitive compare. However, this compare requires the cases match.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Identifying almost similar cells

    Hello Maarten,

    I have added an extra worksheet to the attached workbook to list the matches that weren't found. These are the strings that are the first 6 characters and the last 3 characters whose length did not match. This is what you requested but doesn't make sense to me. Check it out and let me know if this what you wanted.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-13-2011
    Location
    Amsterdam
    MS-Off Ver
    Excel 2007/ 2010
    Posts
    7

    Re: Identifying almost similar cells

    Hi Leith,

    Thank you very much! It worked kind of.

    It returned for instance 1517-C511; I could track this to 1517-CL-511 in system A and 1517-CL-0511 in system B. These are the kind of differences I look for.

    It also returned 6973-c001; however there was no real difference between system A and B in both was 6973-cl-0001...

    How can this be explained?

    Just to give you some more back ground. The first numbers/characters are an activity identifier, the last numbers/characters identify the sub activity (-cl- just divides the two). E.g. 6973-cl-0001 is the first sub activity of activity of 6973.

    I have identified four incorrect data points so far, hopefully those are the only ones.

    Again many thanks

    Best regards

    Maarten

+ 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