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

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

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!

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.

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.