Closed Thread
Results 1 to 23 of 23

Juxtaposing successive levels from parent-child relational data

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Juxtaposing successive levels from parent-child relational data

    Hi! I am trying to reconstruct hierarchical relationships from exported data from a database which contains lots of rows. Some rows do not contain enough of the keys necessary to establish a parent-child relationship, but there are always at least one row and often several which do contain the necessary keys to establish a link between two levels in the hierarchy. There hierarchy consists of three levels, but all three levels may not yet have branched out completely at any given time.

    The example in the attached workbook should make the task clearer. I have tried building up the formulas by using a lot of different array functions from all over the Interwebs, but haven't been able to arrive at something that works all the way and in a neat way.

    I'd appreciate if anyone is able to crack the code for this.

    Best regards,
    Marbleking
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    If you don't mind to use MANY of helper columns. >_<"
    Please try this file (I can not explain about formula and flow, sorry)

    Note : may not work with number store as text.

    Regards.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hi Menem! Thank you very much for this. This is a useful solution and one that doesn't grind Excel to a halt with big calculations.

    I inserted an apostrophe before some of the numbers and your formulas still seemed to work. I would prefer the sorting order to be column 1, then column 2 and then column 3, though. It seems to be somewhat the other way around in your suggestion, with the blanks always at the bottom of each column. Column 1 should keep the values in order, and then the column 2 values that correspond to each of the column 1 values should be in order within themselves, meaning there might be gaps along the way in column 2. And column 3 should likewise be in order respective of the order in column 2.

    If you are able to adjust for the sorting, it would be perfect.

    Also, if anyone is able to come up with a solution that doesn't require helper columns, that would be exciting! Perhaps with the use of the new dynamic array functions, like UNIQUE, SORT, FILTER etc.

    Thank you very much.

    Regards,
    Marbleking

  4. #4
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    Please try this file.

    Regards.
    Attached Files Attached Files

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Juxtaposing successive levels from parent-child relational data

    Please try this file (I can not explain about formula and flow, sorry)
    @ Menem

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Juxtaposing successive levels from parent-child relational data

    Please try at
    F3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    G3
    =IFNA(INDEX($A$3:$A$24,MATCH(TRUE,COUNTIF(G$2:G2,$A$3:$A$24)<COUNTIFS($B$3:$B$24,$A$3:$A$24)*($B$3:$B$24=F3),)),INDEX($A$3:$A$24,MATCH(F3,$B$3:$B$24/ISNA(MATCH($A$3:$A$24,$G$2:G2,)),)))

    H3
    =IFNA(INDEX($A$3:$A$24,MATCH(G3,$B$3:$B$24/ISNA(MATCH($A$3:$A$24,H$2:H2,)),)),"")

    all formula require Ctrl+Shift+Enter
    Attached Files Attached Files
    Last edited by Bo_Ry; 07-02-2020 at 07:07 AM. Reason: correction

  7. #7
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    I will try my best to explain.

    1. Create each 'Level' for each row.

    'Level1'
    G3
    =IF(LEFT(C3,6)="Level1",A3,IF(LEFT(D3,6)="Level2",B3,IFERROR(VLOOKUP(B3,A:B,2,FALSE),"")))

    'Level2'
    H3
    =IF(LEFT(D3,6)="Level2",A3,IF(LEFT(D3,6)="Level3",B3,""))

    'Level3'
    I3
    =IF(LEFT(D3,6)="Level3",A3,"")

    2. Check for how many data are list in table.

    K3 (Have only L1)
    =IF(LEFT(C3,6)="Level1",IF(COUNTIFS(D:D,"Level2*",B:B,A3)=0,1,0),0)

    L3 (Have L1&L2)
    =IF(LEFT(D3,6)="Level2",IF(COUNTIFS(D:D,"Level3*",B:B,A3)=0,1,0),0)

    M3 (Have L1,L2,L3)
    =IF(AND(G3<>"",H3<>"",I3<>""),1,0)

    3. Generate rank order (sorted)

    O3
    =IF(SUM(K3:M3)>0,VALUE(TEXT(IFERROR(RANK(G3,G:G,1),0),"00000")&TEXT(IFERROR(RANK(H3,H:H,1),0),"00000")&TEXT(IFERROR(RANK(I3,I:I,1),0),"00000")),0)

    4. Find 1st data rank (Don't copy down)
    R3
    =SMALL(O:O,COUNTIF(O:O,0)+1)

    5. Check rank of 1st data (and others)
    Q3
    =RANK(R3,O:O,1)

    6. Find 2nd and next
    R4
    =SMALL(O:O,Q3+1)

    7. Find row of data (rank value)
    S3
    =IFERROR(MATCH(R3,O:O,0),0)

    8. Display output (copy down and right 2 columns)
    T3
    =IF($S3>0,INDEX(G:G,$S3),"")

    Regards.

  8. #8
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Thanks a lot menem and Bo_Ry!

    Menem: Since your formula in the O column uses the RANK funtion, which can only rank numbers, I had to insert three more helper columns to convert the data in the G, H and I column to numbers by using the NUMBERVALUE function. The data in these columns will be fetched from data exported from a database, where the serial numbers are stored as text.

    Bo_Ry: I had to insert double negatives (--) in front of the lookup_value in the first Match function in your column H formula, to have it handle numbers coming from text.

    I think both solutions are very impressive, but I tested them with inserting duplicate lines in my raw data table and then none of the solutions returned correct data at the correct place. If you insert a couple of rows somewhere in the table and copy some of the information from the row above down in the new rows, you will see what I mean. Each row in the results table should return a unique relationship between the three levels.

    Would you be able to adjust your solutions to accommodate this? It would be fantastic!

    Best regards,
    Marbleking

  9. #9
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Juxtaposing successive levels from parent-child relational data

    Please try Power Query
    Open Power Query/Get and Transform. Click on New Query.
    Open blank query in the editor, launch Advanced Editor and paste in the following code.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    Could you please show me the sample of your real data pattern (number store as text) ?
    I wish to chekc does they are the same in each row for same data (and find another way to convert to number - if possible)

    Regards.

  11. #11
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hello!

    Menem: I have attached a workbook containing actual data. (The database output may contain several other rows and columns that are not relevant to the problem we are currently discussing, which have been removed for the purpose of clarity).

    Bo_Ry: I have tried your Power Query solution and it seems to work perfectly. Would you be able to adapt it to the updated data that I have attached?

    Regards,
    Marbleking
    Attached Files Attached Files

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Juxtaposing successive levels from parent-child relational data

    Please try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hi Bo_Ry,

    Thanks for the reply. There are a few issues with this solution. For example, if you look at Batch 15014976 (Cell A1077), you see that the Filter Code for this batch is HALB and the Deleted code is false. This means that this batch belongs to Level3 (because of what is written in the Filter Code and Deleted adjacent to this number). It also means that the batch2 number in column Batch2 (Cell B1077), which is 15009018, belongs to Level2 and is the parent of Batch 15014976. If we search for the value 15009018, we will also find it in the Batch column (Cell A966). The Filter Code and Deleted adjacent to this value is SOLUTION and false, respectively, verifying that this batch belongs to Level2. In Batch2 (cell B966) we find the value 15007796. This value is the parent of 15009018 (cell A966) and belongs in Level1. If we search for 15007796 in the Batch column, we will find it e.g. in cell A952. The qualifiers for Level1 values are not Filter Code and Deleted, but Material and Deleted. Only a select list of values in Material qualifies the value in Batch to be a Level1 value. The relevant Material values are listed in J7:J14. The relevant Deleted value is "false", as for all.

    In this example, we had a number for all three levels, but often only Level1, Level1 and Level2, or Level2 exists. All Level1, Level2 and Level3 values will be found in column Batch, and all Level1 and Level2 values will also be found in column Batch2 if and when they become parent to a lower level. Level1 and Level2 may both have 0 or many children.

    There may be many duplicate rows, and the Batch values may also be listed adjacent to irrelevant Batch2 values, but the use of correct values in Filter Code and Deleted should make it possible to not include them. The raw data table will include many columns and rows with various additional and related data that may make it difficult to use a simple remove duplicates function.

    In your solution I see that you make no use of the qualifying codes. Also, your output table include listings like these:

    Level1 - Level2 - Level3
    15007796
    15007796 - 15009018
    15007796 - 15009018 - 15014976
    15009018
    15009018 - 15014976
    15014976

    But this would be the desired outcome for these three values:

    Level1 - Level2 - Level3
    15007796 - 15009018 - 15014976

    Regards,
    Marbleking
    Last edited by Marbleking; 07-04-2020 at 03:53 AM.

  14. #14
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    Please verify, I think that these rows are L3 , but I can't find for L1 value of them

    ie.
    36
    70
    72
    108
    109
    110
    115
    116
    119
    120

    Regards.
    Last edited by menem; 07-06-2020 at 12:48 AM. Reason: Remove too long list

  15. #15
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hi Menem,

    If you filter the table this way, you'll see the various levels:

    Apply filter "HALB" on Filter Code and "false" on Deleted: This gives you the Level3 values in column A (Batch) with the corresponding Level2 values in column B (Batch2).

    Apply filter "SOLUTION" on Filter Code and "false" on Deleted: This gives you the Level2 values in column A (Batch) with the corresponding Level1 values in column B (Batch2).

    Apply filter according to numbers in range J7:J14 on Material and "false" on Deleted: This gives you the Level1 values in column A (Batch).

    And there may be duplicate responses to these filters. But the complete database export contains other columns too, which haven't been included in the example. Therefore, each row as a whole may not be an exact duplicate of any other row.

    Best regards,
    Marbleking
    Last edited by Marbleking; 07-04-2020 at 06:39 AM.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Juxtaposing successive levels from parent-child relational data

    Guys - a bit of thought, please!!!

    Look at all the real estate taken up by the last two posts - don't do this again. Instead, provide Excel WBs containing your checklists. Thanks.

  17. #17
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hi AliGW! Apologies! Have a nice day! Regards, Marbleking

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Juxtaposing successive levels from parent-child relational data

    I'd appreciate your both fixing it, please - you have made the thread really difficult to read. Thanks.

    EDIT: Thanks for fixing yours, Marbleking.
    Last edited by AliGW; 07-04-2020 at 04:14 AM.

  19. #19
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Juxtaposing successive levels from parent-child relational data

    AliGW, sorry for my mistake. >_<

    Marbleking, I'v use this logic for identify L1,L2,L3 or none.

    IF Deleted is true or Batch is blank = none. (result as "" )
    IF Batch not blank and len of Batch2 is 0 = L1
    IF Found Batch2 in Batch = L2
    Else = L3

    as formula in G2
    =IF(OR(E2="true",ISBLANK(A2)),"",IF(AND(NOT(ISBLANK(A2)),LEN(B2)=0),"L1",
    IF(COUNTIF(A:A,B2)>0,"L2","L3")
    ))

    Then I try to find each L's of each row.
    I2 (L1)
    =IF(G2="L1",A2,IF(G2="L2",B2,IF(G2="L3",VLOOKUP(B2,A:B,2,FALSE),"")))

    J2 (L2)
    =IF(G2="L2",A2,IF(G2="L3",VLOOKUP(A2,A:B,2,FALSE),""))

    K2 (L3)
    =IF(G2="L3",A2,"")

    But found some of L1 are N/A#, so, I've stuck at this point. T_T

    Regards.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Hello!

    I have built the logic that I think gets the results that I want, using four x three helper columns and one x results columns.
    However, the formulas required are very memory intensive and will not be practical for real applications.
    The external database exports I am planning to use this logic on consists of between 100.000 and 500.000 rows of various data, of which approximately 500 to 2.000 rows contains the relevant data as reflected in the attached workbook.

    I would therefore be very grateful if someone would be able to transform my example to something more efficient and robust, presumably using VBA, Power Query and/or improved functions.

    Below are the formulas along with some explanation:

    Section 1 (splitting two columns with data from three hierarchical levels into three columns):

    A2:

    Please Login or Register  to view this content.
    B2:

    Please Login or Register  to view this content.
    C2:

    Please Login or Register  to view this content.

    Section 2 (filling in gaps in parent levels to connect as many of the three levels as possible by going backwards from higher to lower levels):

    E2:

    Please Login or Register  to view this content.
    F2:

    Please Login or Register  to view this content.
    G2:

    Please Login or Register  to view this content.

    Section 3 (removing duplicate rows):

    I2:

    Please Login or Register  to view this content.
    J2:

    Please Login or Register  to view this content.
    K2:

    Please Login or Register  to view this content.
    Section 4 (removing incomplete rows where more complete rows exist and inserting zeros for blanks):

    M2:

    Please Login or Register  to view this content.
    N2:

    Please Login or Register  to view this content.
    O2:

    Please Login or Register  to view this content.

    Section 5 (sorting the resulting rows in ascending order by level 1, then by level 2 and lastly by level 3):

    Q2:

    Please Login or Register  to view this content.
    R2:

    Please Login or Register  to view this content.
    S2:

    Please Login or Register  to view this content.

    I am looking forward to your replies. I am sure this solution can be improved significantly.

    Best regards,
    Marbleking
    Attached Files Attached Files

  21. #21
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Juxtaposing successive levels from parent-child relational data

    Just my guess and my last try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Juxtaposing successive levels from parent-child relational data

    Thanks for your effort, Bo_Ry

    Power Query may hold the solution to this conundrum, I'm sure. I am not too familiar with Power Query, so I am not able to identify how your code could be tweaked to yield the exact match with the formula/function version. But thanks again!

    Perhaps someone else is able to work it out? I'd love to see.

    Have a nice day,
    Marbleking

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,370

    Re: Juxtaposing successive levels from parent-child relational data

    Thread closed in favour of one opened in the commercial services section.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 26
    Last Post: 12-30-2018, 04:25 PM
  2. [SOLVED] Parent Child relationship issue: Get parent ID for child
    By masterl1983 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-28-2018, 07:35 AM
  3. Adding parent row data to child row
    By firepitz in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-16-2017, 02:19 PM
  4. Copy data fom parent file to child files with different layouts
    By Marloes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2014, 05:12 AM
  5. [SOLVED] Sum child to parent, with multiple levels
    By HypeTaxx in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-23-2014, 10:38 AM
  6. [SOLVED] Parent child relationships(working out parent item) for each item
    By grphillips in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-21-2013, 05:58 AM
  7. Replies: 3
    Last Post: 07-09-2010, 02:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1