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

Goletian

New Member
Joined
Aug 6, 2013
Messages
10
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:

abc
1*jon888548885.55.56
2ben708542554.56.11
3ben807546554.56.11
4**goji888548898.65.66
5***hung523658998.65.66

<tbody>
</tbody>

* 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:
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top