How can excel ignore certain text when it searches for text?

[jonathynblythe]

Hello,

I have two excel documents in the same workbook... what I wanted to know was, how can I search one document for John Smith where the other document has him listed now as "John Smith attorney"?

I only need to know that John Smith is on the other sheet, if excel searched for him, it will tell me that he's not there because "attorney" is making the code think that it's a totally different name.

Here's the macro:

Please Login or Register  to view this content.

Every time I search the two ranges between the two sheets, it shows me the blue highlight for a name that technically is in the other sheet - in reality it's only a different thing added to the name (Mr, atty, Mrs, etc.). How do I get this VBA code to ignore certain words in a cell so that the code knows to ignore certain words?


Thanks!


Thank you much!
JB

[Leith Ross]

Hello JB,

You need to Change this line ...

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

[jonathynblythe]

BLESS YOU SIR !!!!!!!!!

I have been looking for this answer for MONTHS!!!!!

Thank you, thank you!!!!!!

[Leith Ross]

Hello JB,

Glad to help. Hard to believe it you months to get an answer to this problem.

[jonathynblythe]

Hello again,

I just have one last question... I noticed that if another guy named John Smith is in the sheet, the code will ignore both and not tell me that "John #1" (or #2) is in there. Is there anything I can do about this?

Thanks again!
JB

ADDENDUM:

Maybe it might be possible to state whatever words you want excel to ignore? ("Attorney", "Mr", "Street", etc.)

[Leith Ross]

Hello JB,

It would be easier to answer your questions with a sample workbook that uses some actual data. Seeing before and after examples of what you want to do will speed up the code development and provide you with the results you want more quickly.

[jonathynblythe]

Here's a sample of the workbook:


This is one of the two sheets in the workbook. The one you're looking at now is for the 2010 year, the other sheet (not pictured, but the same format as you see with this sheet) is for the 2011 year.

As you can see from the code posted in the beginning of this thread, that Glaswegian from techsupportforum dot com made for me, it allows you to select the range for one column on the first sheet in the workbook, and then to select a second range on the second worksheet. I first start with the phone number column "F" for range one, then I go to the other sheet and select column "F" for it's phone numbers. The code compares them and if it can't find the phone number on this years sheet, it highlights the phone number cell on the 2010 sheet blue.

Here's the problem, when it does this for the name and or addresses, it will tell me that "John Smith" is not on there, when he really is since he's now "John Smith Attorney". You showed me how to wild card the results, and it did work for me... however, when there are two of the same addresses on the 2011 sheet, it seems to skip over it and move on to the next address on the 2010 sheet. Does this make sense?

Thank you much
JB

[Leith Ross]

Hello JB,

Due to the personal nature of the data, I removed your attachment. If you can save a single sheet to a new workbook and email it to me then I can create the macro and post it here for others to see. Is that doable?

[jonathynblythe]

Well actually, I was just wondering if it's possible to just make sure that the addresses don't get missed when there's more than one of the same throughout the sheet. I don't know what your email is and I'm not able to send you the workbook due to the information, unfortunately. Sorry....

[snb]

Dive into the VBEditor's help (F1)

Scrutinize the method 'find'
Study the parameter xlPart & xlWhole.

[Leith Ross]

Hello jonaythnblythe,

I sent my email address to you by private message.

[jonathynblythe]

Ok, It's done. Thanks again!

[jonathynblythe]

Actually maybe I do have one more question... is there anyway excel can compare two documents where "John Smith" and "Mr John Smith" (the same exact guy) - where afterwards it highlights the "Mr" red with colorindex=3 (or font.colorindex=3) somehow to show that this is now what's different as opposed to highlighting the entire cell red with colorindex=3?

[Leith Ross]

Hello jonathynblythe,

It is doable. It may slow things down a bit. I'll work on making those changes to the macro.

[jonathynblythe]

Wow, you're assembling a whole new macro just for me?? How can I thank you for all this?