0

I have two columns in Microsoft Exel and each one contain a list of cars models. I want to compare the two columns, so the models that exist on the second columns and the first one become red. The two columns are almost identical however there can be some syntax mistakes (misspelled text), exemple:

  • Columns 1: Mercedes C 250, BMW M5, Volkswagen Polo, Audi A5
  • Columns 2: Mercedess 250, BMW M5, Aud A5

I want that those cells become red after the comparison: "Mercedes C 250", "Mercedess 250", "BMW M5", "Audi A5", "Aud A5"

I will be very thankful if someone has a tip for doing quickly that type of comparison with Excel or some other tool.

Naourass Derouichi
  • 773
  • 3
  • 12
  • 38
  • 2
    you can look into [Levenshtein distance](http://en.wikipedia.org/wiki/Levenshtein_distance): _Levenshtein distance has a wide range of applications, for instance, spell checkers, correction systems for optical character recognition..._. Here is [VBA implementation](http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel) – Dmitry Pavliv Mar 15 '14 at 17:39

1 Answers1

-2

You can use Conditional Formatting:

Assuming values in A1:B5, select A1:A5 go to Conditional Formatting add a new rule, choose "Use a formula to determine which cells to format" and write the formula:

=COUNTIF($A$1:$B$5;A1)>1
CRondao
  • 1,883
  • 2
  • 12
  • 10
  • This is not working because "COUNTIF" is applicable only for identical cells comparison, we have syntax mistake in my case. – Naourass Derouichi Mar 15 '14 at 17:12
  • AH you want to compare misspelled text? Well, good luck with that. By the way it is impossible to get an error with that formula, the only thing that can happen is values not signed. – CRondao Mar 15 '14 at 17:25
  • It's possible to get an error if Naourass is using US Excel, which uses commas as function argument separators, in which case they'd want: `=COUNTIF($A$1:$B$5,A1)>1`. – Doug Glancy Mar 15 '14 at 17:31
  • @CRondao Exact, I want to compare misspelled text! Is there a practical for doing that? – Naourass Derouichi Mar 15 '14 at 17:36
  • Really? If he considers that a mistake and you fell the urge to came in his defense, how... what a haste of time, that was said here more than 1000 times... – CRondao Mar 15 '14 at 17:37
  • @NaourassDerouichi I think it will very difficult, eventually you can create a dictionary with all possible misspellings – CRondao Mar 15 '14 at 17:39
  • @CRondao No way because I have more than 100k lines with severals words. Is it possible to match only 4 or 5 five letter of the words in the comparison? Or may be ignoring 2 or 3 letters? – Naourass Derouichi Mar 15 '14 at 17:44
  • 1
    @NaourassDerouichi that you can do, if you want to use only the first five letters, for instance, use =COUNTIF($A$1:$B$5,Left(A1,5) & "*")>1 – CRondao Mar 15 '14 at 17:50
  • @CRondao Ok thank you! What about ignoring 2 or 3 letters? How to achieve this please? – Naourass Derouichi Mar 15 '14 at 17:54
  • 1
    Assuming your comment was to me, I'm not sure how "defense" applies. I was trying to clarify why the answer might yield an error. It's too bad you took it personally. – Doug Glancy Mar 15 '14 at 17:54
  • Maybe so, you tried to clarify something that need no clarification, but some people just like to bug in, your entitle, as every one else, to do it...conversation over – CRondao Mar 15 '14 at 17:57
  • @NaourassDerouichi sorry, but I am the one that it's taking this personally when I am trying to help, others vote down the same answer that is helping you and dont help with anything... – CRondao Mar 15 '14 at 18:07
  • Since SO is a forum, other people might join in the conversation. Sometimes it takes more than one person to help with the answer. Again, I'm sorry it hurt your feelings. – Doug Glancy Mar 15 '14 at 18:14
  • @CRondao Nobody is talking personally :) Even if your answer was useful and will be useful for the future visitors of this post, it was not convenient as a solution to my problem. Same thing for Doug's comment. Let's forget the personal matters please and focus on the problem's solution! – Naourass Derouichi Mar 15 '14 at 18:15
  • @NaourassDerouichi your right, lets focus in the problem in hands. But the second part is more difficult because, depending on the name, you will probably want to ignore different letters, right? – CRondao Mar 15 '14 at 18:21
  • @CRondao Exact, that's what I'm planning to do... The misspelled text must have 1 or 2 wrong/missing letters at the max, so I think that ignoring 1 or 2 letters while comparing will be a great solutions. The problem is that there are some cells that contains text with several words, so I will have to apply the "letter ignoring solution" for each word of each cell string... – Naourass Derouichi Mar 15 '14 at 18:26