How to find matching level 2 asset for each level using a Parent/child relationship/structure

broken keyboard

New Member
Joined
Oct 10, 2017
Messages
6
Hi all,

I am trying to locate the "level 2" asset of each asset number in the list in Column A. A parent/child relationship is shown in Columns F & G. Not sure what the best formula to use here would be, but maybe an array? Not every asset in the hierarchy has an immediate next-level up asset associated. So, for example, an asset at level 7 may have a parent at level 3, then the level 3 asset would have a parent at level 2. The asset at level 2 is the asset I need to return. Any help on the below is much appreciated :)

Asset Hierarchy Structure.xlsx
ABCDEFG
1Asset Number:Hierarchy Level:What is the Level 2 asset for this particular asset?ParentChild
276556573456347454
33453524373474544745374
4232325674745374747434
534957747434353473
694157735347323523
7971357353473235346
8533987353473765565
923523685654436346
10235346643634634535243
1178333668343236224
12353473523622478333
134363465783332323256
1457425852386329
157474344863299871
16236224498713495
174745374398715742
1898713574294157
193474542987197135
20856542574253398
21683432
22863292
2334561
2485231
Asset Hierarchy
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
This isn't pretty, but I think it works. This approach uses help columns H:AC (or AE if you want to shorten a formula) to examine every parent-child relationship and build the multigenerational lineup on a single row. So columns H:L begin with the question "Does the Parent appear as a Child, and if so, what is its Parent?" to go in one direction, with each column to the right referring to its neighbor at left. The formula to do that is pulled to the right until only blanks are generated for all of the rows. Columns M:Q ask a similar question, "Does the Child appear as Parent, and if so, what is it's Child?" to go in the other direction. Again, that formula is pulled to the right until only blanks are generated. Then columns R:AC perform a look up using columns A:B to create a hierarchy mapping for every item in the parent-child multigenerational table. Finally, we return to column A, and for every asset number, we search the multigeneration table (F3:Q21) to find that asset, determine what row in the table it is found (any row where it is found should be fine, but the first is taken), and then we look across that row to find which asset in its family line corresponds to hierarchy level "2". I've separately tabulated the row and column index numbers within F3:Q21 where this occurs, as shown in columns AD:AE, but those same formulas are incorporated in the INDEX formula in column C. If you prefer, the column C formula could be shortened substantially by simply referring to the value in columns AD and AE.
MrExcel20210424.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1123456789101112123456789101112
2Asset Number:Hierarchy Level:What is the Level 2 asset for this particular asset?ParentChildDoes Parent appear as Child? If so, what is it's Parent?and againand againand againand againDoes Child appear as Parent? If so, what is it's Child?and againand againand againand againHierarchy Level of ParentHierarchy Level of Childrowcol
376556573474543456347454     474537474743435347323523 12     3456 75
43453524378565434745447453743456    74743435347323523  231    456  81
5232325676834347453747474343474543456   35347323523   3421   56   101
6349578632974743435347347453743474543456  23523    45321  6    132
7941577863293534732352374743447453743474543456      564321      163
89713578632935347323534674743447453743474543456      564321      183
95339878632935347376556574743447453743474543456      574321      194
1023523634745485654436346     34535243    25     7    12
1123534663474544363463453524385654         572         65
127833366834368343236224     783332E+06   24     67   101
1335347353474542362247833368343    2323256    462    7    12
1443634658565478333232325623622468343        6742        81
155742586329852386329     98713495   12     37   163
1674743443474548632998718523    3495    231    7    12
1723622446834398713495863298523        3721        101
184745374334745498715742863298523   94157    3521   7    12
1998713863295742941579871863298523       57321       132
203474542347454987197135863298523        3721        12
21856542856545742533989871863298523       57321       81
2268343268343101
2386329286329132
243456134745412
258523186329132
Sheet1
Cell Formulas
RangeFormula
H3:H21H3=IFERROR(INDEX($F$3:$F$21,MATCH(F3,$G$3:$G$21,0)),"")
I3:L21I3=IFERROR(INDEX($F$3:$F$21,MATCH(H3,$G$3:$G$21,0)),"")
M3:M21M3=IFERROR(INDEX($G$3:$G$21,MATCH(G3,$F$3:$F$21,0)),"")
N3:Q21N3=IFERROR(INDEX($G$3:$G$21,MATCH(M3,$F$3:$F$21,0)),"")
R3:AC21R3=IFERROR(INDEX($B$3:$B$25,MATCH(F3,$A$3:$A$25,0)),"")
AD3:AD25AD3=AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1)
AE3:AE25AE3=MATCH(2,INDEX($R$3:$AC$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),),0)
C3:C25C3=INDEX($F$3:$Q$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),MATCH(2,INDEX($R$3:$AC$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$2))/($F$3:$Q$21=A3),1),),0))
 
Solution

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
You're welcome. I'm happy to help.
 

Forum statistics

Threads
1,136,954
Messages
5,678,757
Members
419,782
Latest member
gc75150

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