Expand multilevel information

  • Hi. I apologize if something similar has been answered before. I've been struggling with what to search for or how to identify my need with a label.


    In the attached file, I'm looking for a way to combine two reports (Input 1 and Input 2) into a combined report (Output).


    I have an Access file that we've managed to get something close to what we want. But it is difficult to manage and requires post-export editing in Excel to eliminate repetitive information.


    Ideally, I'd like to use formulas, but macros aren't a deal breaker. Thanks in advance for any support that you can offer!

  • Hi and Welcome to the Forum :)


    Thanks for your sample file


    Would you mind if the structure was slightly modified with a Sheet dedicated to Inputs and another one just for the Output ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Carim, that works for me!! Thanks for your help!


    Now that I have an easier way to get my desired info into one worksheet, I might be back later with more questions/enhancements!


    Thanks again for the quick help!!


    Glad it is sorted out ...;)


    Thanks for your Thanks :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Ok, I missed a critical element in my sample file that I attached.


    For Input 2:

    A "parent group" can have many "child groups".


    Group-1...Group-7

    Group-1...Group-16

    Etc.


    In the output, I'd look for a row for each combination:

    1) Org-1...Folder-1...Role-1...Group-1...Group-7...

    2) Org-1...Folder-1...Role-1... Group-1...Group-16...


    Hope that makes sense. (I'm limited on file upload capabilities while at work.)

  • Hi again,


    Attached is your test file


    Proposal is as follows :


    Columns A to E are generated by the Expand Macro

    and

    Columns F to J are generated by Index/Match formulas


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • Here is a formula solution as in :


    1] In "Output" G4, formula copied across to J4 and all copied down :


    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($4:$14)/((0+TEXT(COUNTIF($B$18:$B$30,$E$4:$E$14),"[=]1;0"))>=COLUMN($1:$1)),ROWS($1:1))),"")


    2] In "Output" K4, formula copied across to P4 and all copied down :


    =IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$18:$C$30)/($B$18:$B$30=J4),COUNTIFS($H$4:$H4,$H4,J$4:J4,J4))),"")


    Regards


    Expand Multilevel_rev1(BY).xlsx

  • Hi Carim.


    First, I would like to thank you for your time! It is much appreciated. Second, I would like to apologize for my extreme delay in responding. Priorities shifted, and I wasn't able to commit time to this work.


    After I started entering my data into the spreadsheet, my results weren't matching what I wanted. Your macro and formula were perfect. My input was not. In trying to simplify my request, I omitted information. :/


    In the output table, columns 'Team - Level 2' thru 'Team - Level n' could have duplicated values similar to 'Team - Level 1'. In the attached file, I've highlighted the new example in purple. Again, this scenario could occur at Level 2 thru Level n.


    Please let me know if I'm unclear. Thanks again!

  • Hi bosco_yip.


    I'm going to repeat my response to Carim to you...


    First, I would like to thank you for your time! It is much appreciated. Second, I would like to apologize for my extreme delay in responding. Priorities shifted, and I wasn't able to commit time to this work.


    After I started entering my data into the spreadsheet, my results weren't matching what I wanted. Your macro and formula were perfect. My input was not. In trying to simplify my request, I omitted information. :/


    In the output table, columns 'Team - Level 2' thru 'Team - Level n' could have duplicated values similar to 'Team - Level 1'. In the attached file, I've highlighted the new example in purple. Again, this scenario could occur at Level 2 thru Level n.


    Please let me know if I'm unclear. Thanks again!