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

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.)

I've highlighted additional info in yellow.

Thank you!

Hi Carim. Yes, sorry, I know that was a critical factor in this question!

Quote from Carim

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

Display More

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!

Quote from bosco_yip

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

Display More

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!