Results 1 to 5 of 5

How to efficiently identify all rows indirectly related to specific rows in Excel?

This is a discussion on How to efficiently identify all rows indirectly related to specific rows in Excel? within the Excel Questions forums, part of the Question Forums category; Hi, guys. I don't know the best way to articulate what I'm trying to do, so I've had a hard ...

  1. #1
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Default How to efficiently identify all rows indirectly related to specific rows in Excel?

    Hi, guys. I don't know the best way to articulate what I'm trying to do, so I've had a hard time finding similar use cases on the web. After isolating a select few rows of data, I then need to identify any other rows directly or indirectly related to those original rows I specify. For example:


    • I define "group 1," consisting of 5 rows of columns with names, phone numbers, and IP addresses that are important.
      • The names are the unique identifiers, and may be redundant across rows, but with different IPs, etc.

    • Then, I would want to identify "group 2" which consists of any other rows sharing a phone number or IP address with any row in group 1.
    • Then I would want to identify "group 3" (indirectly related to group 1 via direct relationships with group 2) which consists of any other (not previously grouped) rows sharing a phone number or IP address with any row in group 2.
    • I would continue, until all possible direct and indirect relationships (defined by shared phone number or IP address) are exhausted.
    • Then, I would want a list of all the unique name values for every row in every group formed by direct or indirect relationships to group 1.


    Currently, I'm doing this using only formulas like VLOOKUP, MATCH, INDEX. I'm working with hundreds of thousands of rows, and have an i7 3.10 CPU with 16GB RAM. It's easy to get direct relationships, and a few indirect relationships, but as I branch out beyond a few groups to exhaust all possible indirect relationships, my hardware is overwhelmed and I can't go further. Is there a better way to do what I'm trying to do? Any insight would be greatly appreciated!

  2. #2
    New Member
    Join Date
    May 2013
    Posts
    25

    Default Re: How to efficiently identify all rows indirectly related to specific rows in Excel?

    1) is all of this in one worksheet?
    2) what is the definition of a group?

  3. #3
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Default Re: How to efficiently identify all rows indirectly related to specific rows in Excel?

    1) yes, one worksheet.
    2) all rows and groups consisting of name, phone number, ip address. group 1 is defined arbitrarily using no formulas/logic. group 2 is defined by shared phone numbers and IP addresses with group 1. group 3 is defined by shared phone numbers and IP address with group 2, and so on. the names distinguish each row.

    for instance:

    a b c
    1* jon 8885488 85.55.56
    2 ben 7085425 54.56.11
    3 ben 8075465 54.56.11
    4** goji 8885488 98.65.66
    5*** hung 5236589 98.65.66

    * arbitrarily defined
    ** shares b4 with row 1
    *** shares c5 with row 4, and row 4 is directly related to row 1 because of b4 = b1

    in reality, it would be multiple rows making up a group, but say--using the above sample data--i define group 1 as consisting of only row 1 (arbitrarily). then group 2 would consist of any row not in group 1 that shares a value in common with group 1 in the b or c column--in this case, row 4 would be in group 2 due to shared value b4 = b1. then row 5 would fall into group 3 due to shared value c5 = c4 (group 2). so row 5 would be indirectly related to row 1 due to both sharing something in common with row 4. and this goes on and on for an indefinite number of groups/levels of indirect interrelatedness. im trying to have excel identify every row indirectly related to those i specify in group 1, no matter how distantly related the rows are (based on indirect relationships by shared b or c column values). they dont even have to be broken down into distinct groups.
    Last edited by Goletian; Aug 6th, 2013 at 03:24 AM.

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default Re: How to efficiently identify all rows indirectly related to specific rows in Excel?

    Details notwithstanding, the search term I'd use is 'network relationships excel'. Turns up a few things, including the odd bit of free stuff like:

    Creating Network Graphs in Excel | Educational Technologies Center
    Two plus two equals five for large values of two.

  5. #5
    New Member
    Join Date
    Aug 2013
    Posts
    10

    Default Re: How to efficiently identify all rows indirectly related to specific rows in Excel?

    ohhh, that sounds good. thank you! i will research that.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com