Compare and Count similar words in a cell

X

xtreme07

Board Regular
Joined
Sep 21, 2010
Messages
71
Hi,

Considering we have 2 cells A1 cell and B1 cell
A1 has the folowing group of words: count me in
B1 has the following group of words: me count in

notice there are the same words arranged in different order

What i need is to make the C1 cell (3rd cell) to look into the A1 and take the word "count", then the word "me", then the word "in" and see if any of these appear in the B1 cell. if they do exist then display in the C1 the number 1. else, display 0

i'd appreciate some input on this.


92488398.jpg
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi

This is not clear to me


1 -

xtreme07 said:
What i need is to make the C1 cell (3rd cell) to look into the A1 and take the word "count", then the word "me", then the word "in" and see if any of these appear in the B1 cell. if they do exist then display in the C1 the number 1. else, display 0
Click to expand...

Don't you mean "see if all of these appear in the B1 cell."?

In you example "count" appears in both cels and yet the result is 0.


2 - What if all the words in A1 appear in B1 but B1 has more words, like:

A1 - "count me"
B1 - "count in me"

All the words in A1 appear in B1, but B1 has also "in" that does not appear in A1.

Is it as you wrote, you are only interested if all the words in A1 appear in B1, or do you want also to check the reverse, if all the words in B1 appear in A1?

3 - can a word appear more that once? what to do in that case?

A1- "count me in"
B1- "count in me in"

0 or 1?

4 - the test consists of words separated by spaces. Is this correct?

Please clarify
 
Upvote 0
mancubus said:
may help:

http://www.mrexcel.com/forum/showthread.php?p=955137
Click to expand...

it's pretty good, yet the problem with this is that it does the matching based on total number of words between cells. overall this could be pretty good if i would know how to make some changes to it.

please look at the image below to see why the fuzy ain't working for me.


pgc01 said:
Hi

This is not clear to me



Please clarify
Click to expand...

sorry for confusion. i need to have strictly a result that tells me if B2 has the same words as the cell A1 no matter how many times or how mixed they are into a phrase. tho if i think better, i might want cell C1 to show #2 if there are words that appear more than once ... it might be harder tho and if this is the case, i would be happy with a formula based on the example from the image below.

i hope this answers all your questions. if there are more, please feel free to ask.


please see the image below to see what i need

 
Last edited:
Upvote 0
in reply for pgc01

i forgot to mention that if the words appears more than once i'd be happy if the result shows #2. i am also happy with the cell showing #1, even tho it would be of better help to show #2 as it gives me a better understanding of what's going on inside the cells

the main purpose is to understand how related is the B1 to A1.
 
Upvote 0
Not sure if this is what you want. In the code below I've assumed that the A column cells contain ONLY the keywords you want to find in column B cells.
Code: 
Sub KeyWordsPresent()
Dim rng1 As Range, lRw As Long, i As Long, j As Long, KeyWds As String
Dim vArr As Variant, wdCt As Integer, oRng As Range

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Range("A1", "A" & lRw)
Set oRng = rng1.Offset(0, 2)
For i = 1 To lRw
    KeyWds = rng1.Cells(i, 1).Value
    wdCt = Len(KeyWds) - Len(Replace(KeyWds, " ", "")) + 1
    vArr = Split(KeyWds, " ")
    For j = 0 To UBound(vArr)
        If InStr(1, rng1.Cells(i, 1).Offset(0, 1).Value, vArr(j)) > 0 Then
           oRng.Cells(i, 1).Value = 1
        Else
            oRng.Cells(i, 1).Value = 0
        End If
       
   Next j
Next i
End Sub
 
Last edited:
Upvote 0
Add the following code (FastExcel aka Charles Williams) to your workbook...

_________________________

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
_________________________

<TABLE style="WIDTH: 303pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=405><COLGROUP><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4096" width=115><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3470" width=98><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 86pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=115></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 73pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=98></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>count me in</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>me count in</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>dragon country</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>eak river</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>

D2, copy down:
Rich (BB code): 
=ISNUMBER(LOOKUP(9.99999999999999E+307,
   SEARCH(eval("{"&CHAR(34)&SUBSTITUTE(A2," ",CHAR(34)&","&CHAR(34))&CHAR(34)&"}"),B2)))+0
 
Upvote 0
Revised my earlier post to remove case sensitivity and add an Exit For:
Code: 
Option Compare Text
Sub KeyWordsPresent()
Dim rng1 As Range, lRw As Long, i As Long, j As Long, KeyWds As String
Dim vArr As Variant, wdCt As Integer, oRng As Range

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = Range("A1", "A" & lRw)
Set oRng = rng1.Offset(0, 2)
For i = 1 To lRw
    KeyWds = rng1.Cells(i, 1).Value
    wdCt = Len(KeyWds) - Len(Replace(KeyWds, " ", "")) + 1
    vArr = Split(KeyWds, " ")
    For j = 0 To UBound(vArr)
        If InStr(1, rng1.Cells(i, 1).Offset(0, 1).Value, vArr(j)) > 0 Then
           oRng.Cells(i, 1).Value = 1
        Else
            oRng.Cells(i, 1).Value = 0
            Exit For
        End If
       
   Next j
Next i
End Sub
 
Upvote 0
This is the way that I would go - whether it is the best or not, I do not know.

I have done this for 3 words, but more can be added - let me know how many you need.

The data through which you wish to search is in Cell A1, and the words you want to search for are in D3, seperated by spaces.

First off, seperate the words you have types into D3 into individual words.

in D1, enter this formula:
=IF(ISERROR(FIND(" ",D3))," "&D3&" "," "&MID(D3,1,FIND(" ",D3,1)))

in E1, this one:
=IF(ISERROR(FIND(" ",D3)),"",MID(D3,FIND(" ",D3),FIND(" ",SUBSTITUTE(D3," ","¬",2),1)+2))

and in F1, this one:
=IF(ISERROR(MID(D3,FIND("¬",SUBSTITUTE(D3," ","¬",2)),LEN(D3))&" "),"",MID(D3,FIND("¬",SUBSTITUTE(D3," ","¬",2)),LEN(D3))&" ")

This will seperate the words, with spaces either side, so you are only searching for whole words.
now search for the first word, with the number of instances, in A1 like this (in eg G1):

Code: 
=IF(SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(D1))=D1,1,0))>0,SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(D1))=D1,1,0)),SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(D1)-1)=LEFT(D1,LEN(D1)-1),1,0),IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(D1)-1)=RIGHT(D1,LEN(D1)-1),1,0)))

confirmed with Ctrl+Shift+Enter

you can search for the nest word by dragging this formula to the right.

the second and third words will not function properly if you are only looking for one word. This can easily be solved, but the formula gets confusingly long. Solve it by selecting the entire formula (except the "=" sign), and copying it with Ctrl+c, then typing =if(iserror(Ctrl+v),"",Ctrl+v), or if you have a later version of excel, you can use =iferror(Ctrl+v,"")

I think that works, but shout if there is any problem.
Mark
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
570
Davenil
D
E
  • Question Question
split cell at specific character count but break at a whole word
Replies
6
Views
468
felixstraube
F
M
  • Question Question
Compare cell colour to other cell colour and trigger action automatically
Replies
7
Views
430
Maxime_Excel
M
K
  • Solved
Need help consolidating data from 3 columns. I have a VBA code for QTY but the last column will now be data instead, how do I change code?
2
Replies
10
Views
838
rlv01
R
P
  • Question Question
putting a snippet of code from one to another somehow
Replies
5
Views
304
Patriot2879
P

Forum statistics

Threads
1,223,922
Messages
6,175,406
Members
452,640
Latest member
steveridge

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top