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!

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".

Thanks very much in advance!!!


"Harlan Grove" wrote in message
...
"nb" wrote...
...
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.
...
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)
...
....but that's not quite what I'm looking for.

This can be done with array formulas. If Group 1 were in A1:A4 and Group 2
in B1:B4, and the topmost result in C1, enter the following array formula
in
C1.

=INDEX($B$1:$B$4,SMALL(IF(ISNUMBER(MATCH(IF(COUNTI F($B$1:$B$4,
"*"&$A$1:$A$4&"*"),"*"&$A$1:$A$4&"*",""),TRANSPOSE ($B$1:$B$4),0)),
MATCH(IF(COUNTIF($B$1:$B$4,"*"&$A$1:$A$4&"*"),"*"& $A$1:$A$4&"*",""),
TRANSPOSE($B$1:$B$4),0),1E+300),ROW()))

Fill C1 down until you get #REF! errors.