Consolidate chaos

btardo01

Board Regular
Joined
Oct 6, 2009
Messages
161
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
 

Attachments

  • Help.PNG
    Help.PNG
    31.8 KB · Views: 16

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
899
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
899
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 18, 2019
Messages
899
Office Version
  1. 365
Platform
  1. Windows
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

 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,798
Members
416,983
Latest member
LessThanAverageUser

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
Top