insert three columns and bring the values based on matching with two workbooks

hello
I have two workbooks report1,2 contains data should match in COL B,C,D with file OUTPUT Also IN COL B,C,D then inserting three columns and brings the values in COL sales and purchase and calculate in COL QTY i need show the formula in COL QTY and it should summing the values from files each COL SALES AND PURCHASE and add to file OUTPUT and if there are brands are existed in files and not existed in file output should add to file output as highlighted by yellow but when add them should match col A in files with COL A in file output and add it before total
note : the files in the same folder and may be increase the files also the data in output is increasable in rows I mean in COL A,B,C,D
I put the result in sheet after and the original data in sheet before but should show the result in sheet1 , and every time run the macro should insert three columns with same borders and formatting also formulas .
if this is not clear please inform me

I thought post#3 is clear when I put the formula in last column in J the idea is when insert a new three columns the last column which calculate the values depends on precede the last column as in sheet result1 last column which calculate the values is G and the precede last column is COL G then when calculate and show the values in COL J= 32+65-25 =77 and if I insert a new three columns as in sheet result2 last column which calculate the values is M and the precede last column is COL J then when calculate and show the values in COL M= 77+65-20 =77 the same thing whit the others rows except TOATL row and every time insert a new three columns the formula should be the precede last column + a new purchase - a new sales
I hope this help please check two result result1,2

it shows the total for purchases and sales and qty(stock) for each category for instance category for OIL-208L it shows how calculate Total values the purchases and sales and qty in row total , it calculates for each category separately and I've found some cells when I sum them , they're not within the formula , I fixed it and updated the file

You must review the workbook for yourself if nobody answer.

No one want to do such a "GUESS" work and no one but only you know the details of what you are trying to do.

Sub test()
    Dim x, msg As String, dic As Object
    GetData ThisWorkbook.Path, x, msg
    If Not IsArray(x) Then MsgBox "No files in the forlder": Exit Sub
    If Len(msg) Then MsgBox msg: Exit Sub
    Set dic = CreateObject("Scripting.Dictionary")
    GetDic x, dic
    OutPut "Before", x, dic
    Set dic = Nothing
End Sub

Private Sub GetData(myDir As String, x, msg As String)
    Dim fn As String, cn As Object, rs As Object
    fn = Dir(ThisWorkbook.Path & "\*.xls")
    If fn = ThisWorkbook.Name Then fn = Dir()
    If fn = "" Then msg = "No file": Exit Sub
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;"
        .Open ThisWorkbook.Path & "\" & fn
    End With
    Do While fn <> ""
        If fn <> ThisWorkbook.Name Then
            If IsEmpty(x) Then
                ReDim x(1 To 1)
            Else
                ReDim Preserve x(1 To UBound(x) + 1)
            End If
            On Error Resume Next
            rs.Open "Select `CATOGERY`, `BRAND`, `TYPE`, `MONAFACTURE`, Sum(`Purchase`), Sum(`Sales`) From `PR$` " & _
            "In '" & ThisWorkbook.Path & "\" & fn & "' 'Excel 12.0;''HDR:=Yes;''' Where `CATOGERY` Is Not Null And `BRAND` " & _
            "Is NOt Null And `TYPE` Is Not Null And `MONAFACTURE` Is Not Null Group By `CATOGERY`, `BRAND`, `TYPE`, `MONAFACTURE`;", cn, 3
            If Err.Number <> 0 Then msg = fn & " has problem": Exit Do
            On Error GoTo 0
            x(UBound(x)) = rs.GetRows: rs.Close
        End If
        fn = Dir
    Loop
    Set cn = Nothing: Set rs = Nothing
End Sub

Private Sub GetDic(x, dic As Object)
    Dim i As Long, ii As Long, txt As String, w
    For i = 1 To UBound(x)
        For ii = 0 To UBound(x(i), 2)
            If Not dic.exists(x(i)(0, ii)) Then
                Set dic(x(i)(0, ii)) = CreateObject("Scripting.Dictionary")
            End If
            txt = Join(Array(x(i)(1, ii), x(i)(2, ii)), "")
            x(i)(4, ii) = IIf(IsNull(x(i)(4, ii)), 0, x(i)(4, ii))
            x(i)(5, ii) = IIf(IsNull(x(i)(5, ii)), 0, x(i)(5, ii))
            If Not dic(x(i)(0, ii)).exists(txt) Then
                dic(x(i)(0, ii))(txt) = Array(x(i)(1, ii), x(i)(2, ii), x(i)(3, ii), x(i)(4, ii), x(i)(5, ii))
            Else
                w = dic(x(i)(0, ii))(txt): w(3) = w(3) + x(i)(4, ii)
                w(4) = w(4) + x(i)(5, ii): dic(x(i)(0, ii))(txt) = w
            End If
        Next
    Next
End Sub

Private Sub OutPut(wsName As String, x, dic As Object)
    Dim i As Long, ii As Long, iii As Long, n As Long
    Dim a, b, e, s, txt As String, temp, r As Range
    Application.ScreenUpdating = False
    With Sheets(1).Cells(1).CurrentRegion
        .Columns(.Columns.Count - 2).Resize(, 3).AutoFill _
        Destination:=.Columns(.Columns.Count - 2).Resize(, 6)
        With .Cells(1).CurrentRegion.Offset(2).Resize(.Rows.Count - 2)
            .Columns(.Columns.Count - 2).Resize(, 3).ClearContents
            a = .Value: .ClearContents: .Borders.LineStyle = xlNone
            .Font.Bold = False
            ReDim b(1 To Rows.Count, 1 To UBound(a, 2))
            .Interior.ColorIndex = xlNone
            For i = 1 To UBound(a, 1)
                If a(i, 1) <> "" Then temp = a(i, 1)
                If a(i, 2) <> "TOTAL" Then
                    n = n + 1
                    For ii = 1 To UBound(a, 2) - 3
                        b(n, ii) = a(i, ii)
                    Next
                    If dic.exists(temp) Then
                        txt = Join(Array(a(i, 2), a(i, 3)), "")
                        If dic(temp).exists(txt) Then
                            b(n, UBound(a, 2) - 2) = dic(temp)(txt)(3)
                            b(n, UBound(a, 2) - 1) = dic(temp)(txt)(4)
                            dic(temp).Remove txt
                            If dic(temp).Count = 0 Then dic.Remove temp
                        End If
                    End If
                Else
                    If dic.exists(temp) Then
                        If dic(temp).Count Then
                            For Each e In dic(temp)
                                n = n + 1
                                For ii = 0 To 2
                                    b(n, ii + 2) = dic(temp)(e)(ii)
                                Next
                                b(n, UBound(b, 2) - 2) = dic(temp)(e)(3)
                                b(n, UBound(b, 2) - 1) = dic(temp)(e)(4)
                                dic(temp).Remove e
                            Next
                            If dic(temp).Count = 0 Then dic.Remove temp
                        End If
                    End If
                    n = n + 1: b(n, 2) = "TOTAL"
                End If
            Next
            If dic.Count Then
                For Each e In dic
                    n = n + 1: b(n, 1) = e
                    For Each s In dic(e)
                        For ii = 0 To 2
                            b(n, ii + 2) = dic(e)(s)(ii)
                        Next
                        b(n, UBound(b, 2) - 2) = dic(e)(s)(3)
                        b(n, UBound(b, 2) - 1) = dic(e)(s)(4)
                        n = n + 1
                    Next
                    b(n, 2) = "TOTAL"
                Next
            End If
            With .Resize(n)
                .Value = b
                For Each r In .Columns(3).SpecialCells(2).Areas
                    r.Offset(, .Columns.Count - 3).FormulaR1C1 = "=rc[-3]+rc[-2]-rc[-1]"
                    If r(r.Count + 1, 0) = "TOTAL" Then
                        r(r.Count + 1, 3).Resize(, UBound(b, 2) - 4).Formula = _
                        "=sum(" & r.Offset(, 2).Address(0, 0) & ")"
                        With r(r.Count + 1, 0).Resize(, UBound(b, 2) - 1)
                            .Interior.Color = 11573124
                            .Font.Bold = True
                        End With
                    End If
                Next
                .HorizontalAlignment = xlCenter
                .Offset(, 1).Resize(, .Columns.Count - 1).Borders.Weight = 2
                .Rows.AutoFit
            End With
        End With
    End With
    Application.ScreenUpdating = True
End Sub