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

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

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

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 |
+-------------------------------------------------------------------+

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

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

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

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

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
------------------------------------------------------------------------
greg_overholt's Profile: http://www.excelforum.com/member.php...o&userid=30872
View this thread: http://www.excelforum.com/showthread...hreadid=505505

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

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
------------------------------------------------------------------------
greg_overholt's Profile: http://www.excelforum.com/member.php...o&userid=30872
View this thread: http://www.excelforum.com/showthread...hreadid=505505

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

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.
--
Kevin Vaughn


"greg_overholt" wrote:


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
------------------------------------------------------------------------
greg_overholt's Profile: http://www.excelforum.com/member.php...o&userid=30872
View this thread: http://www.excelforum.com/showthread...hreadid=505505