Is it possible to create nested dictionaries from this sample data?

I'm familiar with Collections and arrays, but now I'm trying to expand into dictionaries. I have a working excel VBA app that creates and stores construction pay applications. It's clunky and poorly coded thanks to yours truly. I've learned much since I wrote this program, and it's time to make needed performance and structural improvements.

Some of the challenges of working with the raw data is...

Creating new pay applications
New pay applications need information from previous application
Editing existing pay applications
Archiving old pay applications
The data is located in excel file on SharePoint and front end excel file is shared among different users.

I feel dictionaries could greatly simplify my code, but not sure if what I'm looking to do is possible. If it is possible, I really only need a nudge in the right direction. If not, do you have a suggestion of the best way to tackle this? I control the data, so I could break it up into separate tables if needed. I'd prefer to stay away from Access or SQL Server on the back end.

My sample data looks like this:

Hopefully this sample data is enough to communicate the pattern. My actual data is a couple thousand rows and 11 columns after Item #.

I'd like to see the dictionary look like this when the dictionary is put into a watch and expanded:

23-001
    1
        1
            Description
            Value
        2
            Description
            Value
23-002
    1
        1
            Description
            Value
        2
            Description
            Value

Looks just like the output of a pivot table...

You do not need to have Access or use an SQL server in order to use SQL on Excel data. The following code will use ADO, connect to the workbook and take the data from Sheet1 as input. Afterwards you can use SQL to work with the data, you can even update your data with SQL statements

Option Explicit

Sub AdoTest_UpdateExcelFile()
    
    Dim xlFile As String
    xlFile = ThisWorkbook.FullName

    ' You have to add Active Data Objects to the referencss
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fld As Field
    Dim sSQL As String

    With cn
        .Provider = "Microsoft.ACE.OLEDB.16.0"
        .ConnectionString = "Data Source=" & xlFile & "; Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With

    sSQL = "SELECT * FROM [Sheet1$]"
    rs.Open sSQL, cn

    rs.MoveFirst
    Worksheets.Add
    
    Dim i As Long
    For i = 0 To rs.Fields.Count - 1
        Cells(1, i + 1).value = rs.Fields(i).Name
    Next i
        
    Range("A2").CopyFromRecordset rs
    rs.Close
    
    
    Sheet1.Activate
    ' One can also update data
    cn.Execute "Update [Sheet1$] SET Project='23-100' WHERE Project ='23-001' ;"
    
    ' In the following statement I left out the column 'value' because that is in conflict woth the SQL Key word VALUE
    ' You can circumvent that though
    cn.Execute "Insert Into [Sheet1$] (Project,Application ,Item,Description) Values('23-003',3,1,'TEXT4')"

End Sub

Further reading

Using ADO with VBA

Dictionaries, Collections, and Classes

• The outer dictionary's keys hold the Projects while each associated item holds another inner dictionary. Each inner dictionary's key holds the Application while each associated item holds a collection of the Property objects containing the 3 (11) properties like Item, Description, Currency... etc.
• BTW, VBE will never show the structure required unless you use arrays. You will have to imagine it.

An Example in a Standard Module e.g. Module1

<!-- language: lang-vb -->

Option Explicit

Sub DictDataTEST()

    Dim dict As Object: Set dict = DictData

    Dim Prop As cProps, pKey, aKey
    
    For Each pKey In dict.Keys
        Debug.Print "Product: " & pKey
        For Each aKey In dict(pKey)
            Debug.Print "Application: " & aKey
            Debug.Print "Properties:"
            For Each Prop In dict(pKey)(aKey)
                Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency
            Next Prop
        Next aKey
    Next pKey

    Debug.Print "1st Properties of 2nd Application of 1st Project (""23-001""):"
    
    Set Prop = dict("23-001")(2)(1)
    Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency

    Debug.Print "2nd Properties of 1st Application of 2nd Project (""23-002""):"
    
    Set Prop = dict("23-002")(1)(2)
    Debug.Print Prop.pId, Prop.pDescription, Prop.pCurrency

End Sub

The Function in a Standard Module e.g. Module1

Function DictData() As Object

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim rg As Range, rCount As Long
    
    With ws.Range("A1").CurrentRegion
        rCount = .Rows.Count - 1 ' exclude headers
        Set rg = .Resize(rCount).Offset(1)
    End With
    
    Dim Data(): Data = rg.Value
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim Prop As cProps, r As Long, Project As String, App As Long
    
    For r = 1 To rCount
        Project = CStr(Data(r, 1))
        If Not dict.Exists(Project) Then
            Set dict(Project) = CreateObject("Scripting.Dictionary")
        End If
        App = Data(r, 2)
        If Not dict(Project).Exists(App) Then
            Set dict(Project)(App) = New Collection
        End If
        Set Prop = New cProps
        Prop.pId = Data(r, 3)
        Prop.pDescription = Data(r, 4)
        Prop.pCurrency = Data(r, 5)
        dict(Project)(App).Add Prop
    Next r
                 
    Set DictData = dict

End Function

Class Module cProps

<!-- language: lang-vb -->

Option Explicit

Private m_pId As Long
Private m_pDescription As String
Private m_pCurrency As String
'
Public Property Get pId() As Long
    pId = m_pId
End Property

Property Let pId(pId As Long)
    m_pId = pId
End Property

Public Property Get pDescription() As String
    pDescription = m_pDescription
End Property

Property Let pDescription(pDescription As String)
    m_pDescription = pDescription
End Property

Public Property Get pCurrency() As String
    pCurrency = m_pCurrency
End Property

Property Let pCurrency(pCurrency As String)
    m_pCurrency = pCurrency
End Property

The Result

Product: 23-001
Application: 1
Properties:
 1            TEXT1         Currency1
 2            TEXT2         Currency2
Application: 2
Properties:
 1            TEXT3         Currency3
 2            TEXT4         Currency4
Product: 23-002
Application: 1
Properties:
 1            TEXT5         Currency5
 2            TEXT6         Currency6
Application: 2
Properties:
 1            TEXT7         Currency7
 2            TEXT8         Currency8
1st Properties of 2nd Application of 1st Project ("23-001"):
 1            TEXT3         Currency3
2nd Properties of 1st Application of 2nd Project ("23-002"):
 2            TEXT6         Currency6