Expand multilevel information

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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 "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim.


    The structure can definitely be modified.


    I included sample info all on one sheet to try and help with ease of presentation. Yet, I forgot to mention this in my post!


    Thanks!

  • 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!!

  • 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 "Smiley" icon, 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.)

  • Would you mind posting your New Input 2 table ... ;)

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

  • Thanks for the update ...


    For your info ... this is not a minor modification ...:rolleyes:

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

  • 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

  • Hello,


    Once you have tested both the macro and the formulas ... feel free to share your comments

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

  • Would very much appreciate your feedback ...

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

  • 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!

  • After almost 20 messages ... seems you have not yet reached ... the start line ...:(

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!