Hello,
I am trying to compare a sequence of words between two cells and show the difference, separated by a comma.
For Example:
Cell A has: The quick brown dog jumped over the lazy dog
Cell B has: The brown mouse jumped over the lazy cat and ran
Desired Result: Column D: NEW WORDS in B not in A & Column E: Original Words in A Not in B
The desired result is in Columns D & E. (Please see attached spreadsheet)
Thanks for you're help.
Last edited by VegasL; 01-25-2015 at 12:38 PM.
Didn't get what are you trying to do?
Can do this with a user defined function. For example
Open the VBA editor by hitting Alt F11.
Please Login or Register to view this content.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.
In a suitable cell, enter = NewWordsInB(A2,B2)
Remember to save the workbook as a macro enabled workbook .xlsm
Martin
Can do this with a user defined function. For example
Open the VBA editor by hitting Alt F11.Please Login or Register to view this content.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.
In a suitable cell, enter = NewWordsInB(A2,B2)
Remember to save the workbook as a macro enabled workbook .xlsm
Thanks mrice, that worked perfectly, appreciate the clear instructions as well. Do you know how to also do for column cell e2, Original Words in A Not in B ?
Maybe put in one or seperate macro?
I guess no way to do this in the traditional formula sense? not familiar with vba...
Here's the code for the other function
Please Login or Register to view this content.
Thanks. In for new code use = OrginalWordsinA(A2,B2) ?
Lastly, will this code work when i have more text in columns a and b for a defined range of cells?
Appreciate your help!
Hi,
enter
=newwords(A1,B1) in a cell where A1 & B1 contain the two strings.
One way
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard,
You're pretty close:
I updated code in cell E2 to read: =newwords(A2,B2) and that almost works. , i think in you're code it considers "test" and "tests" to be the same.
I also added column b for matching words. for column f, i tried flipping around your original, to =newwords(B2,A2) but that didn't work.
I put the desired results in updated attachment in column H..J, would you mind helping update?
Hi,
Swapping them around seems to work for me and =newwords(b2,a2) is returning 'tests' & 'quiz'
I agree the UDF is not distinguishing plurals like 'tests' from the singular 'test'. At the moment as long as the UDF finds a word string in one cell anywhere in the other string then it's not counting that as a different word.
However does not Martin's UDF in post#6 give you exactly what you want - give or take the odd typo like 'orginal'
Richard,
You're right. My bad. Now for column d, where I want to show the matching words, is there a way to do this in all one script?
BTW..thanks to both of you. Really apprecaited.
A small modification to Martin's code will do it.
Please Login or Register to view this content.
Thanks Richard, OIC, so i add another module and then put in column D2 =samewords(A2,B2).
Works great. Do you know how to modify the other scripts so it puts a comma "," after each word?
Ok I got it, put a &"," like stNew = stNew & " " & st_1(x)&","
Last edited by VegasL; 01-27-2015 at 01:27 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks