[VBA] idとparent_idのリストを階層表示

雑にしか書いてないけど

Option Explicit

Private Indent
Private Row

Private Sub SearchChild(id, lb, ub)
        
    Cells(Row, 4 + Indent) = id
    
    Indent = Indent + 1
    Row = Row + 1
    
    Dim i
    For i = lb To ub
        If Cells(i, 2) = id Then
            SearchChild Cells(i, 1), lb, ub
        End If
    Next
    
    Indent = Indent - 1
    
End Sub

Sub Main()
    
    Range(Columns("D"), Columns(Columns.Count)).Clear
    
    Dim i, id, pid
    Dim Root: Set Root = CreateObject("System.Collections.ArrayList")
    
    Dim lb: lb = 2
    Dim ub: ub = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = lb To ub
        
        id = Cells(i, 1)
        pid = Cells(i, 2)
        
        If pid = "" Then
            Root.Add id
        End If
    
    Next
    
    Indent = 0
    Row = lb
    
    For Each id In Root.toArray
        SearchChild id, lb, ub
    Next
    
End Sub

追記：関数でもできちゃう人がいる、すごすぎる

idとparent_idのリストを階層表示するやつ、やってみました #エクセルクイズ

=LAMBDA(target,LET(
stack, LAMBDA(stack,remains,parent_id,padding,LET(
ids, FILTER(INDEX(remains,,1),INDEX(remains,,2)=parent_id),
remains, FILTER(remains,INDEX(remains,,2)<>parent_id),… https://t.co/ARAD3FQBE1 pic.twitter.com/qB632abdsG

— 風柳 (@furyutei) December 5, 2023