1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2007, Compare columns advice

Discussion in 'Business Applications' started by frednil, Feb 19, 2013.

Thread Status:
Not open for further replies.
  1. frednil

    frednil Thread Starter

    Feb 19, 2013
    I have a problem that I think is not that tricky to solve, but I can't figure it out. I'm not completely retarded when it comes to excel, but I'm not at a pro so I come here for help =)

    I have two lists of serialnumbers. In one the SN is totally correct and the other is somewhat correct, but might miss a letter or a 0 is an O.

    I have this function "=IF(ISERROR(MATCH(D1;$A$1:$A$75;0));"";"x")" which detects exact matches. What I am looking for is a function that will first check for exact match and if it's not a match then it should check if there's a near match preferably in %.

    I might be dreaming and this is impossible, but I hope not.

    I realize that most SN of the same product will have a lot of similarities but that's ok. As long is I get an indicator like this I will know if there was a exact match, nothing close to a match or something that might be typo.

    This is what I would want it to look like. To make it easy to spot I have only deleted the last letter in the cases with not an exact match but I would like to be able to detect errors in any position. It should also be said that the SN is from a wide range of products so the length is not always the same. I tried comparing a letter at a time which I think would work if all strings were the same length in all cases?

    Please give me some advice if not a complete solution. Is this something feasible or should I just give up? =)


    edit: Just after I made this post I found out about a tool developed by Microsoft "Fuzzy Lookup Tool" which seems to be exactly what I am looking for. I will install it and try it out.

    MY19HMDSA12197A MJ19HSHY115851X 100
    MJ19HSHY511407Z MJ19HSHY113885 90
    MJ19HSHY113885N HA19HMDP402639R 100
    MJ19HSHY511397V MJ19HSHY113870D 100
    MJ19HSHY113870D MJ19HSHY113879 90
    MJ19HSHY115851X MJ19HSHY5113970 90
    MJ19HSHY113879W MJ19HSHY511407Z 100
    HA19HMDP402639R MY19HMDSA12197A 100
  2. Keebellah

    Keebellah Trusted Advisor

    Mar 27, 2008
    First Name:
  3. frednil

    frednil Thread Starter

    Feb 19, 2013
    Thanks alot! I will look into your suggestion and I like the idea that it might be possible without a addon like Fuzzy Lookup since I planned to make a sheet and distribute to a few colleagues and if we can avoid the add on that would be great!
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090239

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice