Compare and Count similar words in a cell

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:
sorry - corrections:

A1: Text in which you will search
D3: Text your are searching for

D1: =IF(ISERROR(FIND(" ",D3))," "&D3&" "," "&MID(D3,1,FIND(" ",D3,1)))

E1: =IF(ISERROR(FIND(" ",D3)),"",MID(D3,FIND(" ",D3),FIND("¬",SUBSTITUTE(D3," ","¬",2))-(FIND(" ",D3))))

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

G1: =IF(D1=" ","",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

H1 and dragged across: =IF(E1="","",IF(SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(E1))=E1,1,0))>0,SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(E1))=E1,1,0)),SUM(IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(E1)-1)=LEFT(E1,LEN(E1)-1),1,0),IF(MID($A$1,ROW(OFFSET($A$1,,,LEN($A$1),1)),LEN(E1)-1)=RIGHT(E1,LEN(E1)-1),1,0)))) confirmed with ctrl+shift_enter

and we can all see why the code is easier, but I figured since I had chosen this way, it ought at least to be right.
 
Upvote 0

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.
this forum rocks :)
best forum ever and i've read many forums.

i very much appreciate your help on this as i need to solve this problem like yesterday :) i'm struggling with this for a while now and because of it i can't analyze my data.


formulas tested:
- Aladin Akyurek - problem with this as it shows 0 for any simple variation that i use

- High Plains Grifter - also shows 0. see the image below. i've used the same cells you mentioned

highplainsgrifter.jpg



formulas Not tested: JoeMo 's formula and the reason is because i don't know how to use it.

i know the trick with the alt+f11 and i know how to use Aladin Akyurek vba, but i just lack at using JoeMo 's formula. i'd appreciate some tips on this JoeMo

Edit: High Plains Grifter - just saw you new formulas .. testing now .. back in 1 min
 
Upvote 0
@ High Plains Grifter - it is working :)
any chance we can make this work for any keyword size ? asking as i'll be using varied keyword sizes (between 2 - 8 words / group of words) and at the moment this formula is for fixed keyword size.
 
Upvote 0
What variation do you mean? The formula is designed for two cells, both housing strings whose parts are separated by a space.

i was wrong actualy saying it returns 0 for different variations.
on the second testing it returned 1 indeed there is an issue tho; it returns 1 for the E3 (see the image below)

aladinakyurek.jpg


i'd really appreciate it if you'd be able to help me on this as you seem very close to what i need.
 
Upvote 0
you can extend it as far as you like - the only problem is that you need to separate the words - the easy way to solve this, of course, is to enter each word in a separate cell. I can work out a way to separate the words for an indefinite number though - I'll see what I can do.

Just saw your last post - I sling in / delete a few dollar signs etc and repost - E3 is probably still searching B2.
 
Last edited:
Upvote 0
i was wrong actualy saying it returns 0 for different variations.
on the second testing it returned 1 indeed there is an issue tho; it returns 1 for the E3 (see the image below)

aladinakyurek.jpg


i'd really appreciate it if you'd be able to help me on this as you seem very close to what i need.

Any substring from A appearing in B will yield a 1. Since A3's substring "today" is in the string that B3 houses, we get 1. What is the issue with that? Note that the formula does not count.
 
Upvote 0
Any substring from A appearing in B will yield a 1. Since A3's substring "today" is in the string that B3 houses, we get 1. What is the issue with that? Note that the formula does not count.

well, i was wondering if it's possible to make E3 return 1 if all the substrings in the cell A1 meet the substrings of B1 or viceversa; else retun 0
 
Upvote 0
Progression and simplification:

Enter Words to search for in B3
Enter Text to be searched through in A2 and down

First word in D1
=IF(B3="","",IF(ISERROR(FIND(" ",B3))," "&B3&" "," "&MID(B3,1,FIND(" ",B3,1))))

Second word in E1
=IF(ISERROR(FIND(" ",B3)),"",IF(LEN(B3)-LEN(SUBSTITUTE(B3," ",""))=1,MID(B3,FIND(" ",B3),LEN(B3))&" ",MID(B3,FIND(" ",B3),FIND("¬",SUBSTITUTE(B3," ","¬",2))-(FIND(" ",B3)))))

Third word in F1
=IF(ISERROR(MID(B3,FIND("¬",SUBSTITUTE(B3," ","¬",2)),LEN(B3))&" "),"",MID(B3,FIND("¬",SUBSTITUTE(B3," ","¬",2)),LEN(B3))&" ")

find first word, with count:
=IF(D$1="","",SUM(IF(MID($A2,ROW(OFFSET($A$1,,,LEN($A2),1)),LEN(D$1))=D$1,1,0),IF(MID($A2,1,LEN(D$1)-1)=RIGHT(D$1,LEN(D$1)-1),1,0),IF(RIGHT($A2,LEN(D$1)-1)=LEFT(D$1,LEN(D$1)-1),1,0)))
Confirm with ctrl+shift+enter

drag this around to cover D2:F...whatever

things got a bit complicated - I had a smoke and a step back and clarity has happened a bit. I will sort out the word separating for 8+ words tomorrow - bed time now :¬)
 
Upvote 0
High Plains Grifter - very nice; is working great i need tho more flexibility as i'm using thousands of keywords that are 2-8 words long. if this ain't possible hopefully i'll find a way to use your formula as a macro.

JoeMoe - your formula is outputs same results as Aladin's formula and that's that it returns 1 if any of the words from A1 is found in B1.

so, i'm wondering if it's possible to make C1 return 1 if all the substrings in the cell A1 meet the substrings of B1 or viceversa; else retun 0
 
Upvote 0

Forum statistics

Threads
1,223,927
Messages
6,175,437
Members
452,641
Latest member
Arcaila

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