+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    01-26-2006
    Posts
    4

    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

  2. #2
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    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!
    Attached Images Attached Images

  3. #3
    Kevin Vaughn
    Guest

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


  4. #4
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    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

  5. #5
    Registered User
    Join Date
    01-26-2006
    Posts
    4
    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

  6. #6
    Kevin Vaughn
    Guest

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1