Process and Indentation

[Pestomania]

I am working through a large excel sheet that I am hoping to get built into a Smartsheet at some point, but am working on a proof of concept in Excel first.

I have a document that has numerous tabs:
- Compiled
- Parent Processes
- Sub Processes
- Equipment
- Third Party

I cannot find a way to put the entire document on this through XL2bb so I have inputted through numerous different inserts below:

Book1
	A	B	C	D
1	Parent Process 1: Open the door
2		Sub-Process 1: Insert Key
3			Equipment 1: House Keys
4		Sub-Process 2: Turn Key
5		Sub-Process 3: Turn Knob
6		Sub-Process 4: Open Door
7			Equipment 2: Front Doorway
8		Sub-Process 5: Walk through door
9		Sub-Process 6: Close door
10		Sub-Process 7: Lock Door
11
12	Parent Process 2: Do the laundry
13		Sub-Process 8: Grab laundry hamper
14			Equipment 4: Hamper
15		Sub-Process 9: Walk hamper to washer
16		Sub-Process 10: Open the washer lid
17			Equipment 3: Washer
18				Third Party 1
19				Third Party 2
20				Third Party 3
21		Sub-Process 11: Input laundry into washer
22		Sub-Process 12: Grab detergent
23		Sub-Process 13: Measure detergent
24		Sub-Process 14: Pour detergent
25		Sub-Process 15: Return detergent
26		Sub-Process 16: Close lid
27		Sub-Process 17: Turn washer knob
28		Sub-Process 18: Select mode
29		Sub-Process 19: Click Start Button
30
31	Parent Process 3: Load the dishwasher
32		Sub-Process 20: Open Dishwasher
33			Equipment 5: Dishwasher
34				Third Party 1
35				Third Party 2
36				Third Party 3
37		Sub-Process 21: Grab dishes
38		Sub-Process 22: Place dishes in dishwasher
39		Sub-Process 23: Grab dish detergent
40		Sub-Process 23: Put dish detergent in dishwasher
41		Sub-Process 23: Close detergent compartment
42		Sub-Process 23: Close dishwasher load
43		Sub-Process 23: Select Mode
44		Sub-Process 23: Click Start Button
Compiled

Each of the tabs have a "parent process" field that allows you to connect a certain row to a parent process. I am hoping to find a way to copy the data from the background tables and place them into the compiled tab (preferably with indentation and grouping) so that you can see it all together.

Book1
	A	B	C
1	Title	Description	Concat
2	Parent Process 1	Open the door	Parent Process 1: Open the door
3	Parent Process 2	Do the laundry	Parent Process 2: Do the laundry
4	Parent Process 3	Load the dishwasher	Parent Process 3: Load the dishwasher
Parent Processes

Cell Formulas
Range	Formula
C2:C4	C2	=CONCAT([@Title],": ",[@Description])

Book1
	A	B	C	D
1	Title	Description	Parent Process	Concat
2	Sub-Process 1	Insert Key	Parent Process 1: Open the door	Sub-Process 1: Insert Key
3	Sub-Process 2	Turn Key	Parent Process 1: Open the door	Sub-Process 2: Turn Key
4	Sub-Process 3	Turn Knob	Parent Process 1: Open the door	Sub-Process 3: Turn Knob
5	Sub-Process 4	Open Door	Parent Process 1: Open the door	Sub-Process 4: Open Door
6	Sub-Process 5	Walk through door	Parent Process 1: Open the door	Sub-Process 5: Walk through door
7	Sub-Process 6	Close door	Parent Process 1: Open the door	Sub-Process 6: Close door
8	Sub-Process 7	Lock Door	Parent Process 1: Open the door	Sub-Process 7: Lock Door
9	Sub-Process 8	Grab laundry hamper	Parent Process 2: Do the laundry	Sub-Process 8: Grab laundry hamper
10	Sub-Process 9	Walk hamper to washer	Parent Process 2: Do the laundry	Sub-Process 9: Walk hamper to washer
11	Sub-Process 10	Open the washer lid	Parent Process 2: Do the laundry	Sub-Process 10: Open the washer lid
12	Sub-Process 11	Input laundry into washer	Parent Process 2: Do the laundry	Sub-Process 11: Input laundry into washer
13	Sub-Process 12	Grab detergent	Parent Process 2: Do the laundry	Sub-Process 12: Grab detergent
14	Sub-Process 13	Measure detergent	Parent Process 2: Do the laundry	Sub-Process 13: Measure detergent
15	Sub-Process 14	Pour detergent	Parent Process 2: Do the laundry	Sub-Process 14: Pour detergent
16	Sub-Process 15	Return detergent	Parent Process 2: Do the laundry	Sub-Process 15: Return detergent
17	Sub-Process 16	Close lid	Parent Process 2: Do the laundry	Sub-Process 16: Close lid
18	Sub-Process 17	Turn washer knob	Parent Process 2: Do the laundry	Sub-Process 17: Turn washer knob
19	Sub-Process 18	Select mode	Parent Process 2: Do the laundry	Sub-Process 18: Select mode
20	Sub-Process 19	Click Start Button	Parent Process 2: Do the laundry	Sub-Process 19: Click Start Button
21	Sub-Process 20	Open Dishwasher	Parent Process 3: Load the dishwasher	Sub-Process 20: Open Dishwasher
22	Sub-Process 21	Grab dishes	Parent Process 3: Load the dishwasher	Sub-Process 21: Grab dishes
23	Sub-Process 22	Place dishes in dishwasher	Parent Process 3: Load the dishwasher	Sub-Process 22: Place dishes in dishwasher
24	Sub-Process 23	Grab dish detergent	Parent Process 3: Load the dishwasher	Sub-Process 23: Grab dish detergent
25	Sub-Process 23	Put dish detergent in dishwasher	Parent Process 3: Load the dishwasher	Sub-Process 23: Put dish detergent in dishwasher
26	Sub-Process 23	Close detergent compartment	Parent Process 3: Load the dishwasher	Sub-Process 23: Close detergent compartment
27	Sub-Process 23	Close dishwasher load	Parent Process 3: Load the dishwasher	Sub-Process 23: Close dishwasher load
28	Sub-Process 23	Select Mode	Parent Process 3: Load the dishwasher	Sub-Process 23: Select Mode
29	Sub-Process 23	Click Start Button	Parent Process 3: Load the dishwasher	Sub-Process 23: Click Start Button
Sub-Processes

Cell Formulas
Range	Formula
D2:D29	D2	=CONCAT(@[Title],": ",[@Description])

Cells with Data Validation
Cell	Allow	Criteria
C2:C29	List	=Parent_Processes

Book1
	A	B	C	D
2	Equipment 1	House Keys	Sub-Process 1: Insert Key	Equipment 1: House Keys
3	Equipment 2	Front Doorway	Sub-Process 4: Open Door	Equipment 2: Front Doorway
4	Equipment 3	Washer	Sub-Process 10: Open the washer lid	Equipment 3: Washer
5	Equipment 4	Hamper	Sub-Process 8: Grab laundry hamper	Equipment 4: Hamper
6	Equipment 5	Dishwasher	Sub-Process 20: Open Dishwasher	Equipment 5: Dishwasher
Equipment

Cell Formulas
Range	Formula
D2:D6	D2	=CONCAT([@Title],": ",[@Description])

Cells with Data Validation
Cell	Allow	Criteria
C2:C6	List	=SubProcesses

Book1
	A	B	C
1	Title	Description	Parent Process
2	Third Party 1	Electrical Company	Equipment 3: Washer & Equipment 5: Dishwasher
3	Third Party 2	Water Company	Equipment 3: Washer & Equipment 5: Dishwasher
4	Third Party 3	Costco	Equipment 3: Washer & Equipment 5: Dishwasher
Third Party

Cells with Data Validation
Cell	Allow	Criteria
C2:C4	List	=Equipment

Here is an image of what I would really like to see in the end:

I really hope this will be possible.

 

[Fuji]

This will change the data format in Compiled sheet, so better make a back up first.

Sub test()
    Dim i&
    With Sheets("compiled")
        With .Range("a1", .Cells.SpecialCells(11))
            For i = 2 To .Columns.Count
                .Columns(i).IndentLevel = (i - 1) * 2
            Next
            On Error Resume Next
            .SpecialCells(4).Delete xlShiftToLeft
            On Error GoTo 0
        End With
    End With
End Sub
/code]