Comparing Groups
 

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!

Comparing Groups
 

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.