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