How to find number of pairs of strings from list of strings?

[greg_overholt]

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]

The matrix looks bad on the screen, the numbers are supposed to be in
columns, that i've attached an image of what it should look like here.

Thanks!


+-------------------------------------------------------------------+
|Filename: matrixpic.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4271 |
+-------------------------------------------------------------------+

 

[Kevin Vaughn]

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.

 

[greg_overholt]

HI Kevin,

thanks for trying to help me out! Now this small example is to be used
actually for a 25 x 25 matrix populating these cells from a list of
8000 rows all between 1 and 10 columns full of data.

Any ideas for a larger implementation??

Thanks!!

Greg

 

[greg_overholt]

HI Kevin,

thanks for trying to help me out! Now this small example is to be used
actually for a 25 x 25 matrix populating these cells from a list of
8000 rows all between 1 and 10 columns full of data.

Any ideas for a larger implementation??

Thanks!!

Greg

 

[Kevin Vaughn]

I was afraid you were going to say that. I was hoping someone would come up
with a more elegant solution. I am afraid mine would not be able to handle
something that large (or even much larger than the 3 columns it was currently
dealing with.) Unfortunately, some of the things I tried while I was doing
this just wouldn't work for me which is why the formula I came up with was as
long as it was. Sorry. Unless someone has a better idea, I can't think of a
fomulaic way of doing this. It should be doable using VBA though.