Comparing Two Columns - Multiple Words in Same Same Word

  • Thread starter Thread starter Blah
  • Start date Start date
B

Blah

I am trying to compare two groups of words together and would like to
be able to do the following:

Compare group 1 words against group 2 words and create and identify
any words in group 2 that contain any of the terms (including multiple
terms)from group 1.

For instance:

Group 1 Group 2 Identified Words
one one one
two nine onethree
three eight
four onethree

I have used this (in column c) to identify single words:

=COUNTIF(A:A,b1)
=COUNTIF(A:A,b2)
=COUNTIF(A:A,b3)
and so on...

.....but that's not quite what I'm looking for.

Any help would be greatly appreciated.

Many Thanks!
 
I think I'd use a helper column that gives me an indicator and then use
data|filter|autofilter to get the ones that matched:

I put your data in A2:B5 (allowed for headers) and put this in C1:

=IF(COUNT(SEARCH($A$2:$A$5,B2))>0,"Used","not used")

Hit ctrl-shift-enter instead of just enter. This is an array formula. If you
do it correctly, excel will wrap curly brackets {} around your formula.

Adjust the range and drag it down. Then you can use Data|Filter|Autofilter to
see the Used or unused list.
 
=IF(COUNTIF($A$2:$A$5,"*"&B2&"*"),"Used","not used")

Should also work and uses fewer functions. Not an array formula.

Assumes there is a string to test in the cell referred to in the second
argument (B2 above).

for your example with a double word in column B, it should probably be
reversed

=If(countif($b$2:$B$5,"*"&A2&"*"),"Used","Not Used")

Regards,
Tom Ogilvy
 
Thanks so much for your help guys. Unforunately I've had to revise my
question. Here is what I've posted on another string (I'm such a newbie... I
created two strings by accident) -

FIRST REVISION
Thanks so much! It works very well! Is there still a way to get the exact
word too?

Example: Get the "one" and the "onethree" from the last example?

Secondly, I have now run into an issue that the "group 1" list now spans 2
columns (a and b - yes it's that big... and no I can't really use a db
:-).

So, I would now like to compare "c" against "a" and "b" and display in "d".
Please note that I would still need to look for combinations from "a" and
"b" in one word.

Example:
"A" "B" "C" Identified Words ("D")
one eight one one
two nine nine onethree
three ten eight tenfour
four onethree
tenfour

Also, please assume I will need to reference the entire "a" &"b" rows
against "c".


SECOND REVISION

Oh yeah. One other thing. Column "d" (sorry, i said row "d" before) should
only display results that are exactly a combination (or single) match with
"a" and "b" when referencing "c".

Example: In this example, "onenight" would not appear in column "d" because
"night" is not listed in column "a" or "b"

"A" "B" "C" Identified Words ("D")
one eight one one
two nine nine onethree
three ten eight tenfour
four onethree
tenfour
onenight

Thanks again for your help!



Tom Ogilvy said:
=IF(COUNTIF($A$2:$A$5,"*"&B2&"*"),"Used","not used")

Should also work and uses fewer functions. Not an array formula.

Assumes there is a string to test in the cell referred to in the second
argument (B2 above).

for your example with a double word in column B, it should probably be
reversed

=If(countif($b$2:$B$5,"*"&A2&"*"),"Used","Not Used")

Regards,
Tom Ogilvy



Dave Peterson said:
I think I'd use a helper column that gives me an indicator and then use
data|filter|autofilter to get the ones that matched:

I put your data in A2:B5 (allowed for headers) and put this in C1:

=IF(COUNT(SEARCH($A$2:$A$5,B2))>0,"Used","not used")

Hit ctrl-shift-enter instead of just enter. This is an array formula.
Click to expand...
If
Click to expand...
 
Back
Top