Compare 2 columns and display result in 3 column also with part words

[abjac]

Hi I have been fighting with this long time. I have in the real sheet1 column A around 1026 names and in column B around 546 names.
I need to compare partly and display the result in column E.
More than one thing give me problems. In cell A1 and cell B1 i have the word N KOCHS SKOLE For some reason it dont find a match there. I have tried find different clean macros but the same problem. I am afraid that in the real sheet it will be more than this one and cant find out why.
Try have a look at my test sheet.
I need the macro to find partly. As you can see it in the test sheet cant find the match just there is dot or example a -
So that is a problem . Its basically type control F and example write KOCH . Here it will find 3 examples . I have changed all to uppercase. But it should also not be necessary if i have the right macro i think. So in the real sheet i cant count with this.
My macro color the matches yellow in column B. But i think it it could find it partly, it would be better to write the matches in column E seperately.
So then after you can fast check if its really a correct match.
Try have a look at my test sheet. I have written some in column E. Wanted result and also what is the problem.

Please have a look my test sheet would really be nice to solve. And i need it partly matches, like when you make a control F and search

P.s here is the code i use for colour but i think its not good and would like change so it can display the result in column E

Please Login or Register  to view this content.

Sincerely Abjac

[JLGWhiz]

column A is showing no space after SKOLE while column B has a space after. All of the other names I checked had a space after the last character of the name. It is odd that using xlPart would not still find the match. I have no idea how this can occur.

[abjac]

Yes i guess its not the space or it should not be. Its very weird. So its not solved sure. But thanks for the reply

[JLGWhiz]

Yes i guess its not the space or it should not be. Its very weird. So its not solved sure. But thanks for the reply

If your data is downloaded from the internet, what appears to be space could be a non printing character other than a space, but even then the xlPart should pick up the name Unless there is a different character between the text in the cells, then it would not detect the xlPart. I did not check for that, and am not sure how to go about checking it. But if you retype both values in their respective cells and run the code and it finds the match, then it would pretty much confirm that there was a hidden character between the text strings. Logically, there has to be a difference somewhere between the beginning and ending of that string of text for it not to find a match in the other range.

I just did an ASCII check on each character in both strings, using the worksheet in the link. There is no non-printing characters in the strings and they match character for character except for the extra space in column B. So that shoots my hidden character theory in the foot. I am at a complete loss on this one. Here is the code I ran to check the ASCII values. your worksheet was the active sheet at runtime.

Please Login or Register  to view this content.

[abjac]

HI JLGWhiz .
And thanks for the answer, early morning here soon go work. Yes its very very weird. Before i posted it, i ran several codes cleaning macros from internet, And no matter what i did and try, it did not give the right result. My question is if my code is correct ? But i think it is. The problem for me of course is that in the real sheet, where i have to find the matches. I know it will not be correct.
So its a quite big problem.
Thanks for trying. I hope some more will take this challenge, and try figure out, what its wrong. Because this is really weird
So all who want to try , use the test sheet and try figure it out. I have tried so much with this one.
So dont hold your self back. Hope some can figure it out.
Sincerely Abjac

[jindon]

Try

Please Login or Register  to view this content.

[JLGWhiz]

Try

Please Login or Register  to view this content.

This finds it OK, but I would still like to know why the other code didn't find it.

[abjac]

Hi. JIndon. First home from work now. Sorry the late reply but big time difference. And thanks so much for your answer.
I tried the code but i get an error in this line

.FormatConditions.Add xlExpression, , "=countif(a1,b1&""*"")>0"

I use excel 2010 dont know if that mean some.

Sincerely Abjac

[Fluff13]

column A is showing no space after SKOLE while column B has a space after. All of the other names I checked had a space after the last character of the name. It is odd that using xlPart would not still find the match. I have no idea how this can occur.

As the col B value has an extra space it won't be found ("SKOLE " is not part of "SKOLE"), however if the find was switched so that you looked for the col A value in col B, then it would be found ("SKOLE" is part of "SKOLE ")

[JLGWhiz]

As the col B value has an extra space it won't be found ("SKOLE " is not part of "SKOLE"), however if the find was switched so that you looked for the col A value in col B, then it would be found ("SKOLE" is part of "SKOLE ")

My longevity is showing. I looked at that and concluded that same thing, but failed to realize the code was looking in reverse. Second set of eyes always helps.

[abjac]

Hi and thanks for the answers. That means the space after the word in skole? I though it would not matters. Same to remove dots and example -. Then it will find it yes. But i thought it was not that important. I cant make jindons code work it give me an error.
What are your proposal if i have to run this code in the real sheet with more names? How can i be sure it will be correct ?
I can remove dot and - yes but how to remove the space after the last word skole ? I am just surprised this means much. That it cant find it because of this. Sincerely abjac

[JLGWhiz]

Simple change to original code.

Please Login or Register  to view this content.

[abjac]

Hi. JLGWhiz

That works great but what about the characters dot and - and others maybe. Cant the code not find it with them or it need to be changed. But this solve the trim problem and spaces sure. Thanks . The other would like to have also to be sure in the original sheet the result will be correct.
By the way would it not be necessary also to trim in column A to be sure ?


Sincerely
Abjac

[abjac]

Hi. JLGWhiz
Just saw your last update. Not totally what i want. What i need to display in column E is the matches. Its because i need see if they are related to be sure. And the macro find part matches.
Example
N KOCH SKOLE Match
N KOCH SKOLE Match
KOCH SKOLE Match
KOCH KITCHENS " it will display KOCH KITCHENS because of the word KOCH but this is another company. So that i can fast see. So i need the matches to be displayed in column E and not the non matches-
But also now it dont find the matches with symbols as . , - and others. The 3 white now are matches but it dont find because of the dot.

Hope i explain it ok.
Sincerely
Abjac

[JLGWhiz]

This will put the matches in column E. As for the ones with (-), (,), (.) or other symbols which prevent matches, they are not matches and I don't know how to tell which ones need to have the symbols and which ones don't. You could run a 'Replace' macro to remove symbols, but in doing so, you might create matches thar are not true matches. I think it is better to manually review the exceptions cause by the symbols and make manual corrections to the data base, rather than try to do it by code.

Please Login or Register  to view this content.

[abjac]

HI JLGWhiz .

Yes maybe you are right about that. I think this will work fine. Thanks so much for all your help. Its really nice and good help thanks a lot.

Sincerely Abjac.

[jindon]

.FormatConditions.Add xlExpression, , "=countif(a1,b1&""*"")>0"

I use excel 2010 dont know if that mean some.

Should work, see attached.

[abjac]

Hi Jindon and thanks
I tried in your sheet and it still give me a run time error in this line

.FormatConditions.Add xlExpression, , "=countif(a1,b1&""*"")>0"

Sincerely Abjac

[jindon]

Don't know why
Try change that line to

Please Login or Register  to view this content.

Or this colors the cell(s)

Please Login or Register  to view this content.

[abjac]

Hi Jindon and thanks
The first code still give the same error. But the second color it so weird. But thanks a lot for the work . Dont know why the first give this error

Sincerely Abjac