Cross reference tables that could have multiple data

[tnc]

I have a cross reference table that lists WBS (down to level 3) on the row and the different departments who are responsible for each task across the top. I would like the System(s) that is/are used for each corresponding level 3 WBS and department to be listed. This cross reference table is all formula based and is reading off of a reference table on another worksheet.

[pre]

Reference Data Table
WBS   | Level 1 | Level 2     | Level 3    | Level 4 | Level 5   | Department | System
1          Item 1                                                  Department 1
1.1        Item 1    Sub Item 1                                    Department 1
1.1.1      Item 1    Sub Item 1    Process 1                       Department 1
1.1.1.1    Item 1    Sub Item 1    Process 1    Task 1             Department 1
1.1.1.1.1  Item 1    Sub Item 1    Process 1    Task 1 SubTask 1   Department 1 System 1
1.1.1.1.2  Item 1    Sub Item 1    Process 1    Task 1 SubTask 2   Department 1 System 1
1.1.1.1.3  Item 1    Sub Item 1    Process 1    Task 1 SubTask 3   Department 1 System 2
1.1.1.1.4  Item 1    Sub Item 1    Process 1    Task 1 SubTask 4   Department 1 System 1
1.1.1.1.5  Item 1    Sub Item 1    Process 1    Task 1 SubTask 5   Department 1 System 3

OPTION 1 - Cross Reference Table
DEPARTMENT 1
1          Item 1
1.1        Item 1 - Sub Item 1
1.1.1      Item 1 - Sub Item 1 - Process 1      System 1
System 2
System 3
In option 1 I would need a formula that would automatically recognize the unique systems and force the next level 3 to the next row after all systems have been listed.

OPTION 2 - Cross Reference Table
DEPARTMENT 1
1          Item 1
1.1        Item 1 - Sub Item 1
1.1.1      Item 1 - Sub Item 1 - Process 1      System 1, System 2, System 3

[/pre]
Option 2 may be easier and is possible using a vlookup off of the WBS number however the reference table would need a formula for the cell that corresponds to any Level 3/System cell. A concatenate would be possible but I would need the concatenate to be dynamic since I am unsure of how many level 4 or 5s belong to that level 3.

 

[Faseeh]

Hi tnc,


You can get quicker replies if you upload a sample file, and welcome to the forums.


Faseeh

 

[tnc]

Here is the link to access my sample file


https://www.dropbox.com/s/1gk2iqeffjrz055/Business%20Process.xlsx


Thanks for your help in advance

 

[tnc]

Anyone have any solutions to recommend?