Appendix Thread. App Index Rws() Clms() Majic code line Codings for other Threads, Tables etc.)

[DocAElstein]

I missed the point, ( possibly ), with the OPs original data, saying he had like this
1,2, 3 , 3 , 3 , 4 , 4 , 4 , 4, 4 , 4 , 4 . 4 , 4 , 4 , 4 , 4
, but wanted this:
1,2, 3 , 4 , 4 . 4 , 4 , 3 , 4 , 4 , 4 , 4 , 4 , 3 , 4 , 4 , 4
I missed the point ( possibly ) that there could be more than one level 2 and that maybe the levels could go on a lot further above level 4. Maybe that additional information is obvious to most people? It is not to me. The more flexible open ended requirement would explain all the recursioning, explorer tree view type things discussed.

Never mind.. , a restricted scenario could still be useful to investigate for another solution.
Restricted solution
Restrictions:
One Big Boss , level 1
, a deputy who does all his work, Level 2
, or rather organises the line managers, level 3
, who in turn have all the workers organised beneath them, level 4

Macro Sub AlanAlmostGotThePointAgain()
Rem 0 I bring the data into an array in one go, to do some things a bit more efficiently, but this solution is still not a reduce the interaction with the workbook to 2 instances: reading data, writing the result
Rem 1 Based on the restrictions, this simply adds the first few lines in the final data for output, in the re ordered column order.

Rem 2a
A basic formula is used in an “Evaluate Range” type VBA code line. It’s based on a basic spreadsheet formula of the type
=IF(C5:C19=$B$4;ROW(B5:B19);0)
In words, what this is doing is:
For the level 2, the one position 2, LR0201 , is searched for in the POSITION REPORTING column C. The result is returned in the form of spreadsheet row number, and form the test data will look like this


5
6
7
0
0
0
0
0
0
0
0
0
0
0
0

In Rem 3 , this information gives us the count of level 3s, Lvl3s , and then in the first bit of Rem 4, Rem4a , we use this information to from the input array, the information at the correct position in the final output data array , ( using the running count position variable, Dw to give the required position in the final output data array )

Rem 4
This section is a typical inner loop within an outer loop type situation. Rem 4a in the outer loop section deals with the level 3 positions in the final output array, - at each of ( three in the sample data, ) level 3s we have a similar “Evaluate Range” formula to that used previously, - in this case, the formula in Rem 4b , based on this sort of spreadsheet formula,
=IF(C8:C19=$B$5;ROW(C8:C19);0)
, is used to give us the row within the input data to find each set of level 4s reporting to any particular level 3.
For example, on the case of the first outer loop, ( CntInds3 = 1 ) we look for a POSITION REPORTING of LA0101 , and obtain a spread of results of the following form from that single line evaluate range type formula


0
0
0
0
0
13
14
15
16
0
0
0

The inner loop of section Rem 4c deals with giving us the data in the output data array for those ) in the example data, 4 for the first outer loop, ) found level 4s reporting to the level 3 being considered in the outer loop.


Here is a full coding with some extra 'comment notes

'
Sub AlanAlmostGotThePointAgain() '        https://eileenslounge.com/viewtopic.php?p=306916&sid=baf68db6f023ebc9d65767c7abf9e19d#p306916
Rem 0 worksheets data info
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
Dim arrIn() As Variant: Let arrIn() = Ws1.Range("A1").CurrentRegion.Value2                          '    Ws1.Range("A1").CurrentRegion.Resize(Ws1.Range("A1").CurrentRegion.Rows.Count + 1).Value2
Dim arr1DArrays() As Variant ' https://eileenslounge.com/viewtopic.php?p=306912#p306912   https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19940&viewfull=1#post19940    https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page54#post19940
 ReDim arr1DArrays(1 To UBound(arrIn(), 1)) '                                                                         ReDim arr1DArrays(1 To UBound(arrIn(), 1) - 1)                                                    ' Each element will be a row in the final output - see links in last line
Dim Lr As Long: Let Lr = UBound(arrIn(), 1)
Rem 1 some initial lines in the final output, based on the   Restrictions  of  one Boss and 1 deputy, so in other words one level 1 and one level 2
 Let arr1DArrays(1) = Application.Index(Ws1.Cells, 1, Array(1, 4, 2, 5, 6))     '   Ws1.Range("A1:E1").Value2  '
 Let arr1DArrays(2) = Application.Index(Ws1.Cells, 2, Array(1, 4, 2, 5, 6))
 Let arr1DArrays(3) = Application.Index(Ws1.Cells, 3, Array(1, 4, 2, 5, 6))
 Let arr1DArrays(4) = Application.Index(Ws1.Cells, 4, Array(1, 4, 2, 5, 6))
Rem 2a
Dim Dw As Long: Let Dw = 4 ' The main data row for output.  Dw is like a running count keeping note of the next line to add output data to
                                                                                                                     'Dim Lvl As Long: Let Lvl = 2
Dim srchVl As String: Let srchVl = arrIn(Dw, 2)
Dim arrInds3() As Variant: Let arrInds3() = Ws1.Evaluate("=IF(C5:C" & Lr & "=$B$4,ROW(B5:B" & Lr & "),0)")
Rem 3b
Dim Inds3 As Long
    For Inds3 = 1 To UBound(arrInds3(), 1)
     If arrInds3(Inds3, 1) = 0 Then: Dim Lvl3s As Long: Let Lvl3s = Inds3 - 1: Exit For
                                                                                                                                '    If arrInds3(Inds3, 1) = 0 Then Let Dw = Dw + Inds3 + 2: Dim Lvl3s As Long: Let Lvl3s = Inds3 - 1: Exit For
                                                                                                                                ' Let arr1DArrays(arrInds3(Inds3, 1) - 2) = Application.Index(Ws1.Cells, arrInds3(Inds3, 1), Array(1, 4, 2, 5, 6))
    Next Inds3
Rem 4
Rem 4a
'  now we want to investigate all the level 4s reporting to all the level 3s
Dim CntInds3 As Long ' Looping all level 3s
    For CntInds3 = 1 To Lvl3s ' Looping all level 3s
     Let Dw = Dw + 1
     Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, 5 + CntInds3 - 1, Array(1, 4, 2, 5, 6))
    Rem 4b
    Dim arrInds4() As Variant: Let arrInds4() = Ws1.Evaluate("=IF(C" & 5 + Lvl3s & ":C" & Lr & "=$B$" & 5 + CntInds3 - 1 & ",ROW(C" & 5 + Lvl3s & ":C" & Lr & "),0)")
    Rem 4c
    Dim CntInds4s As Long
        For CntInds4s = 1 To UBound(arrInds4(), 1)
            If arrInds4(CntInds4s, 1) = 0 Then
            
            Else
             Let Dw = Dw + 1 '
             Let arr1DArrays(Dw) = Application.Index(Ws1.Cells, arrInds4(CntInds4s, 1), Array(1, 4, 2, 5, 6))
            End If
        Next CntInds4s
    Next CntInds3

Rem 5 Output - convert the 1D array of 1D array output rows to a 2D range form
 Let Range("AE1").Resize(Lr, 5).Value2 = Application.Index(arr1DArrays(), Evaluate("=ROW(1:" & Lr & ")"), Evaluate("=COLUMN(A:E)"))
End Sub

---

Final results and simplified coding in next posts