Compare two columns - words by word - display number of word match
Hi Gents,
A big challenge!
I am searching for a code for comparing two columns of data and get the word match.
Following is the example.
* Col A is the list where we will search
* Col B has the search string
* We need to search the words in cel B2 in range A2:A8 list
* we need to show the word match result in C2,d2,d3 (full/partial/none)
* we need to color each type of match in different color. (optional)
Capture.JPG
Please check by yourself because i am coding on my phone and didn't check yet.
Use array formula on C2:E2
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by AliGW; 10-24-2018 at 03:32 AM. Reason: Unnecessary quotation removed.
Please Login or Register to view this content.
Last edited by AlphaFrog; 10-17-2018 at 08:19 PM.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
If there is 'kiwi apple mango' in column A, is it considered as partial match?
UDFs
1) Formula in
C2: =PartialMatch(B2,$A$2:$A$8)
D2: =FullMatch(B2,A2:A8)
E2: =NoMatch(B2,A2:A8)
2) To a Standard code module
Please Login or Register to view this content.
Good Question. Yes. it is please.
if Kiwi apple mango then partial match
only Mango Kiwi Apple - is a full match
if not possible, its fine. i can add a formula somehow to the next column to fix it.
thank you again.
Hi,Originally Posted by huuthang_bd
I try this code and it give Value# error.
Thank you.
Last edited by AliGW; 10-24-2018 at 03:33 AM. Reason: Quotation shortened.
Hi,
This works fine and its very fast.
Thank you
Last edited by AliGW; 10-24-2018 at 03:34 AM. Reason: Quotation shortened.
HiPlease Login or Register to view this content.
This works fine, thank you for the colors.
sorry i did not describe it in the first place.
i need this down like a formula,
Capture2.JPG
Thank you so much anyways for the code.
Last edited by AliGW; 10-24-2018 at 03:35 AM. Reason: Quotation shortened.
Gents,
hope i am not breaking any forum rules. but this another question in regard to the above solution.
Now we are very happy that all your codes giving the number of matches (full/partial/none). We went crazy seeing the partial match numbers. (its so high)
Thanks to "AlphaFrog", we got another requirement,
Is it possible (of course it is), we can display the list of partial and full match below like the image below,
Capture3.JPG
If you search multiple terms in rows in column B, then coloring the values in column A is sort of pointless. A reference in column A may be no match for one search word in column b and an exact match for another. So what color should it be?
Last edited by AliGW; 10-24-2018 at 03:35 AM. Reason: Quotation shortened.
Guru,
I agree with you. Your code made us think again to approach this in a different view.
So, we are NOT going to copy B3, b4 and so on like formula.
We will keep only one search text, but we need your help coding to list the partial match below. as per my last post above. (cell b 13 and below)
Thank you again.
Last edited by AliGW; 10-24-2018 at 03:35 AM. Reason: Unnecessary quotation removed.
Gentlemen,
Thank you again for your help. really appreciate your time and sharing knowledge helping people.
Kindly help to finalize this code
We need a one final push..
All participants - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you and will follow the same next time.
"Enthusiastic self-taught user of MS Excel who's always learning!" the sentence fit for me as well
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks