Filling in gaps in rows by deducing relationships across table

Hi,

I have a Table 1 with three columns that is made up of rows containing data in one or more of the columns A, B, C. The data are fragments which may have a relationship to other fragments in the table. A single A can point to many Bs which again can point to many Cs. A single C can point to only one B which again can point to only one A.

I have created formulas in cells K14, K15 and K16 (Table 2) that deduces the relationship between the fragments and creates new connections where logically possible. I need help finding a better formula solution that isn't as memory intensive and I am looking for a single dynamic array formula in cell K14 which will spill the results across the three output columns K, L and M.

The formulas I have found so far are these:

K14:

Please Login or Register  to view this content.

K15:

Please Login or Register  to view this content.

K16:

Please Login or Register  to view this content.

Please see attached example workbook, if you'd have a look.

Best regards,
Marbleking

Hi,

This one worked to create a three column wide spilled array:

Please Login or Register  to view this content.

But I'm sure it is possible to come up with a more efficient dynamic array solution. I have updated the workbook with the new formula if someone would like to have a try.

Best regards,
Marbleking

Thanks for the more trimmed solution, Bo_Ry! That worked better.

Regards,
Marbleking