Separate and align rows from two separate sheets based on a cell value.

Here's my take on that:

Option Explicit

Sub Consolidate()
Dim wsOUT As Worksheet, NR As Long, FR As Long, i1 As Long, i2 As Long, c As Long, LR As Long
Dim MyARR1 As Variant, MyARR2 As Variant, Started As Boolean

Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Internal Source Data")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    MyARR1 = .Range("A3:D" & LR).Value
    .Range("C:C").NumberFormat = "@"
End With
With ThisWorkbook.Sheets("Outside Source Data")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    MyARR2 = .Range("A3:D" & LR).Value
    .Range("H:H").NumberFormat = "@"
End With
LR = 1

Set wsOUT = ThisWorkbook.Sheets("Desired Result")
wsOUT.UsedRange.Offset(2).ClearContents
wsOUT.UsedRange.Offset(, 9).ClearContents
NR = 3

For i1 = 1 To UBound(MyARR1)
    If c = 0 Then
        c = MyARR1(i1, 1)
        FR = NR
    End If
    If MyARR1(i1, 1) = c Then
        wsOUT.Range("A" & NR).Value = MyARR1(i1, 1)
        wsOUT.Range("B" & NR).Value = MyARR1(i1, 2)
        wsOUT.Range("C" & NR).Value = "'" & MyARR1(i1, 3)
        wsOUT.Range("D" & NR).Value = MyARR1(i1, 4)
        If FR = NR Then
            wsOUT.Range("J" & NR).FormulaR1C1 = "=SUMIF(C3,RC3,C4)"
            wsOUT.Range("K" & NR).FormulaR1C1 = "=SUMIF(C8,RC8,C9)"
            wsOUT.Range("L" & NR).FormulaR1C1 = "=C11-C12"
        End If
        
        NR = NR + 1
    End If
    If i1 = UBound(MyARR1) Then
        GoTo NextSet
    ElseIf MyARR1(i1 + 1, 1) <> c Then
NextSet:
        For i2 = LR To UBound(MyARR2)
            If MyARR2(i2, 1) = c Then
                Started = True
                wsOUT.Range("F" & FR).Value = MyARR2(i2, 1)
                wsOUT.Range("G" & FR).Value = MyARR2(i2, 2)
                wsOUT.Range("H" & FR).Value = "'" & MyARR2(i2, 3)
                wsOUT.Range("I" & FR).Value = MyARR2(i2, 4)
                If i2 = UBound(MyARR2) Then Exit For
                FR = FR + 1
            ElseIf Started = True Then
                Started = False
                NR = WorksheetFunction.Max(FR, NR) + 1
                LR = i2
                c = 0
                Exit For
            End If
        Next i2
    End If
Next i1

Application.ScreenUpdating = True

End Sub

Note, I've removed the blank rows at the top of the source sheets.

The macro assumes all the codes needed exist in Internal Source Data sheet. The Outside data can have fewer data sets. As long as both sets are sorted the way your samples were, it should work even on larger data sets.

You are correct about the second parameter in the Arrays being the number of columns. But be careful to adjust the SUMIF formulas as well. Currently they are referencing columns 3,4 and 8,9. Those would change if you added columns or moved things around.

With that much data, adding the SUMIF formulas as we go actually slows down the macro more than needed. So I've updated it to add them at the very end instead. I lengthened the data set to 1000 rows and it's pretty fast, still.

Option Explicit

Sub Consolidate()
Dim wsOUT As Worksheet, NR As Long, FR As Long, i1 As Long, i2 As Long, c As Long, LR As Long
Dim MyARR1 As Variant, MyARR2 As Variant, Started As Boolean, StartTIME As Double

Application.ScreenUpdating = False
StartTIME = Now

With ThisWorkbook.Sheets("Internal Source Data")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    MyARR1 = .Range("A3:D" & LR).Value
    .Range("C:C").NumberFormat = "@"
End With
With ThisWorkbook.Sheets("Outside Source Data")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    MyARR2 = .Range("A3:D" & LR).Value
    .Range("H:H").NumberFormat = "@"
End With
LR = 1

Set wsOUT = ThisWorkbook.Sheets("Desired Result")
wsOUT.UsedRange.Offset(2).ClearContents
wsOUT.UsedRange.Offset(, 9).ClearContents
NR = 3

For i1 = 1 To UBound(MyARR1)
    If c = 0 Then
        c = MyARR1(i1, 1)
        FR = NR
    End If
    If MyARR1(i1, 1) = c Then
        wsOUT.Range("A" & NR).Value = MyARR1(i1, 1)
        wsOUT.Range("B" & NR).Value = MyARR1(i1, 2)
        wsOUT.Range("C" & NR).Value = "'" & MyARR1(i1, 3)
        wsOUT.Range("D" & NR).Value = MyARR1(i1, 4)
        NR = NR + 1
    End If
    If i1 = UBound(MyARR1) Then
        GoTo NextSet
    ElseIf MyARR1(i1 + 1, 1) <> c Then
NextSet:
        For i2 = LR To UBound(MyARR2)
            If MyARR2(i2, 1) = c Then
                Started = True
                wsOUT.Range("F" & FR).Value = MyARR2(i2, 1)
                wsOUT.Range("G" & FR).Value = MyARR2(i2, 2)
                wsOUT.Range("H" & FR).Value = "'" & MyARR2(i2, 3)
                wsOUT.Range("I" & FR).Value = MyARR2(i2, 4)
                If i2 = UBound(MyARR2) Then Exit For
                FR = FR + 1
            ElseIf Started = True Then
                Started = False
                NR = WorksheetFunction.Max(FR, NR) + 1
                LR = i2
                c = 0
                Exit For
            End If
        Next i2
    End If
Next i1

With wsOUT.Range("A3:A" & Rows.Count).SpecialCells(xlConstants)
    For i1 = 1 To .Areas.Count
        wsOUT.Range("J" & .Areas(i1).Cells(1).Row).FormulaR1C1 = "=SUMIF(C3,RC3,C4)"
        wsOUT.Range("K" & .Areas(i1).Cells(1).Row).FormulaR1C1 = "=SUMIF(C8,RC8,C9)"
        wsOUT.Range("L" & .Areas(i1).Cells(1).Row).FormulaR1C1 = "=C10-C11"
    Next i1
End With
'wsOUT.Range("J:L").Value = wsOUT.Range("J:L").Value
Application.ScreenUpdating = True
MsgBox "Done - processing time: " & Format(Now - StartTIME, "h:mm:ss")
End Sub