I thought this would be easy but it turns out there was a lot of combinations
to account for. I won't be surprised if someone comes up with a more elegant
solution, but what I came up with is this (I believe I account for all
possible cominations in the test data)

=SUMPRODUCT(--($A$1:$A$4=B$8),--($B$1:$B$4 =$A9))+
SUMPRODUCT(--($A$1:$A$4=$A9),--($B$1:$B$4=B$8))+SUMPRODUCT(--($A$1:$A$4=B$8),--($C$1:$C$4=$A9))+SUMPRODUCT(--($A$1:$A$4=$A9),--($C$1:$C$4=B$8))+SUMPRODUCT(--($B$1:$B$4=B$8),--($C$1:$C$4=$A9))+SUMPRODUCT(--($B$1:$B$4=$A9),--($C$1:$C$4=B$8))

Bob Cat Tree Dog is in B8 to E8 and

Bob
Cat
Tree
Dog
is in A9 through A12. Formula is in B9 copied through E12.
--
Kevin Vaughn


"greg_overholt" wrote:


Hi,

I want to find out the number of pairs from a list of rows all having
anywhere from 1-10 strings.

Ex:

1 2 3
1 Bob Cat Dog
2 Dog Bob
3 Cat Bob Tree
4 Tree


This set of 4 entries (each row having 1-10 strings) to populate this:

Bob Cat Tree Dog
Bob 2 1 1
Cat 2 1 1
Tree 1 1 0
Dog 1 1 0

So Bob and Cat are chosen together in 2 rows, want to see what are most
common pairing for a study.

I was looking at CountIF, but doesn't have any parameters to check row
by row, that ideally looking for the cell for cat/bob - =countif(a row
in data contains both "cat" and "bob")

Any thoughts? This concept is pretty standard that there should be a
simplier way then having to write a VB script.

Any thoughts would be fantastic!!

Thanks!
Greg


--
greg_overholt
------------------------------------------------------------------------
greg_overholt's Profile: http://www.excelforum.com/member.php...o&userid=30872
View this thread: http://www.excelforum.com/showthread...hreadid=505505