VBA modification from earlier solution by Fabrice Lambert to make this comparison work

I had this question after viewing Challenge for  VBA Experts to compare the two strings, original and revised and return the differences.

Fabrice Lambert was so kind helping me with the code that almost works but leaves out some of the words.

I have attached the workbook that illustrates the problem, i am hoping that VBA experts could help me solve this problem.

Thank you.

2017-12-15-18_04_42-Book1.xlsb---Exc.pngBook1.xlsb
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
1. The solution you accepted did a word difference comparison.  That is very different from the phrase (substring) difference you seek.

2. The example you posted in your original question does allow "DOD" to be the correct value in column C4.

Maybe it is a good time for you to define your problem more accurately.
1
aikimarkCommented:
What do you want to do about punctuation and capitalization?
1
Ejgil HedegaardCommented:
Try attached.
There are 2 functions TextAdded and TextRemoved, where the input is the cells in columns A and B.
See columns I and J.
The functions are identical, except for the part for the result, added or removed, but I think it is better than using an array function for both in the same function.
The texts are split to words by space, so if a word is followed by comma or punctuation it is handled as part of the word.
The compare is case sensitive.

The functions use 2 words next to each other in Main text, and search Revised text for the same.
When found, look at the next word, and continue until no match.
Mark all words in the sentence as found, and continue with the next 2 not found words in Main text.

Then search for single word match.
Results are not matched words in Main or Revised text.
Text-changes-Book1.xlsb
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FloraAuthor Commented:
Ejgil Hedegaard

Thanks a Million!  I was becoming hopeless fearing that it might not be possible to achieve what I was looking for.  you are very kind, you just made my day. it worked perfectly in all scenarios of my data
0
FloraAuthor Commented:
thank you aikimark
0
aikimarkCommented:
Did you test this thoroughly?
0
FloraAuthor Commented:
Yes, aikimark.
i tested it thoroughly. so far, i have not found any problem. it does the job.

thanks.
0
aikimarkCommented:
Any three-word (or more) phrase differences in your tests?
0
FloraAuthor Commented:
yes.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.