Consolidate chaos

[btardo01]

I have a file that has a list of client names in column C F & H. If a client is listed on the same row as another (for example, in cell C12 and F12) that means they are related. client ABC Company listed in cell C4 and XYZ Company listed in cell H4.....I know they are related because they on the same row. The problem is that company ABC might also be listed in F22 with MNO company listed in H22. So, ABC, XYX and MNO companies are all related. I need some sort of way to go through and create on consolidated list where every account that is tied to another or its family members get put together on a single row.

So ultimately, on a new sheet or something, I would see ABC company in cell A1, XYZ Company in cell B1 and MNO Company in C1 because on the original sheet they were all connected.

I've attached an image. The top is original data. The bottom is what I was hoping to create on a new tab. All connected accounts are listed on one row. The connected accounts might not always be listed on same row. They might be connected through a 3rd account on a completely separate row.

Any chance someone can help me make this happen?

thanks

 

[KRice]

Two questions:
1. What version of Excel are you using? (you should update your profile to include that)
2. Do the columns between C, F, and H contain information?

 

[CA_Punit]

This Formula can help. Lets try if you are using office 365.

My xl2bb is not working. I am attaching the Screenshot

=SUBSTITUTE(ARRAYTOTEXT(UNIQUE(INDEX(INDEX(B4:D15,INDEX(AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12)),ROW(INDIRECT("A1:A"&COUNT(1/(1/AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12))))))),{1,2,3}),INT((ROW(INDIRECT("A1:A"&COUNTA(INDEX(B4:D15,INDEX(AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12)),ROW(INDIRECT("A1:A"&COUNT(1/(1/AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12))))))),{1,2,3}))))-1)/H5)+1,MOD(ROW(INDIRECT("A1:A"&COUNTA(INDEX(B4:D15,INDEX(AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12)),ROW(INDIRECT("A1:A"&COUNT(1/(1/AGGREGATE(14,6,IFERROR(SEARCH(G5,B4:D15),0)*ROW(A1:A12),ROW(A1:A12))))))),{1,2,3}))))-1,H5)+1),FALSE,FALSE)),G5,"")

 

[CA_Punit]

 

[CA_Punit]

Also, i missed few things, i my example I missed ABCCOM is related to Renway and Renway is related to Bookget. So i think VBA Will be required or power-query might help..
I don't know how to relate all through formula.. Someone else may come up with Ideas.

I would request @Eric W

 

[CA_Punit]

I have worked on a simple formula which can find values upto Second Relation
For Eg If A and B are related and B and C are related then it can Show all A, B and C,


Please find the shared link as my Excel2bb has some issue

Mr Excel Consolidation Probelm.xlsx

Microsoft Excel Workbook

1drv.ms