Creating an excel hierarchy

Question

I am trying to create a hierarchy in excel from a large data source (multiple thousands of entries) and I am trying to create a macro which can convert my dataset.

An example of the data I am trying to convert (not real data):

However, I need it in one of the two formats:

Example 1:

Example 2 (preferred):

I am really new to the excel macro world and I hope I can get some help.

Answer 1

If you don't want a pivot table for that you can use the following code to read your data into a tree structure of dictionaries.

This can then be used to generate the desired output:

Option Explicit

Public Sub Example()
    ' read data into array
    Dim Data() As Variant
    Data = Range("A2:G6").Value
    
    ' create a tree
    Dim RootTree As Object
    Set RootTree = CreateObject("Scripting.Dictionary")
    
    ' fill tree with data
    Dim iRow As Long
    For iRow = LBound(Data, 1) To UBound(Data, 1)
        Dim Parent As Object
        Set Parent = RootTree
        
        Dim iCol As Long
        For iCol = LBound(Data, 2) To UBound(Data, 2)
            If Not Parent.Exists(Data(iRow, iCol)) Then
                Parent.Add Data(iRow, iCol), CreateObject("Scripting.Dictionary")
            End If
            Set Parent = Parent(Data(iRow, iCol))
        Next iCol
    Next iRow
    
    ' output tree
    PrintTree RootTree
End Sub

Private Sub PrintTree(ByVal Tree As Object, Optional ByVal Level As Long = 0)
    Dim Key As Variant
    For Each Key In Tree.Keys
        Debug.Print String(Level * 2, " ") & Key
        If VarType(Tree(Key)) = 9 Then
            PrintTree Tree(Key), Level + 1
        End If
    Next
End Sub

So for example from this data

it would generate the following output

USA
  City
    New York
      Subway
        Singal
          V1.1
            SIG-0004
            SIG-0005
        Access point
          AP(11/04)
            AP-12
      Road
        Singal
          V4.2
            SIG-0034
            SIG-0035

For outputting in cells you can use

Private Sub OutputTree(ByVal Tree As Object, ByVal StartOutput As Range, Optional ByVal Level As Long = 0)
    Static iRow As Long
    
    Dim Key As Variant
    For Each Key In Tree.Keys
        StartOutput.Offset(RowOffset:=iRow, ColumnOffset:=Level).Value = Key
        iRow = iRow + 1
        If VarType(Tree(Key)) = 9 Then
            OutputTree Tree(Key), StartOutput, Level + 1
        End If
    Next
End Sub

and call it like

OutputTree RootTree, ThisWorkbook.Worksheet("Output").Range("A1")

Answer 2

Just do a Pivot Table using option Outline:

Design the layout and format of a PivotTable

Take all fields into rows section.

That's all you need. It will do exactly your request of first image.

UPDATE: Pivot Tables sort A-Z by default. But you can customize the sorting dragging each option, so Road would appear after Subway as example.