Cleaning up data

Hello everyone,
I have a file which lists inventory by store. The problem is, the store numbers are listed first, then the inventory is listed underneath. Each item is seperated by a SKU (unique identifier). The width of each inventory/store listing is either 13 or 14 units wide.
I am trying to get all of this information in a format which is one line tall and about 200 lines wide.
For example:
SKU, Description, Size, Inventory Quantity Store 00, Inventory Quantity Store 01, Inventory Quantity Store 02, ...Inventory Quantity Store 500..., etc. Please keep in mind I have about 25,000 lines of informatin in my real files, so a macro approach is preferred.
Could you point me in the right direction? I attached a file for better understanding. Thank you!

Why do you want to do this? Unless you specifically are wanting to do this for data entry purposes for a legacy system, you would be better off putting your data in a table, and then use a pivot table to produce the output you describe.

Ie, Create a table with the following headings
Sku, description, size, location, quantity.

Then insert a pivot table.

Would that work?

Try

Option Explicit

Sub test()
Dim a, i As Long, ii As Long, w(), n As Long, txt As String
Dim AL As Object, e, s
Set AL = CreateObject("System.Collections.ArrayList")
n = 1
a = Range("a1").CurrentRegion.Value
txt = a(3, 4) & vbLf & a(2, 4)
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1) Step 2
        If a(i, 1) = "" Then Exit For
        If Not .exists(a(i, 1)) Then
            ReDim w(2)
            n = n + 1
            w(0) = n
            Set w(1) = _
            CreateObject("Scripting.Dictionary")
            w(2) = VBA.Array(a(i, 1), a(i, 2), a(i, 3))
            .Item(a(i, 1)) = w
        End If
        w = .Item(a(i, 1))
        For ii = 5 To UBound(a, 2)
            If a(i, ii) <> "" Then
                If Not AL.contains(a(i, ii)) Then
                    AL.Add a(i, ii)
                End If
                w(1)(a(i, ii)) = w(1)(a(i, ii)) + a(i + 1, ii)
            End If
        Next
        .Item(a(i, 1)) = w
    Next
    AL.Sort
    ReDim a(1 To n + 1, 1 To AL.Count + 4)
    a(1, 1) = "SKU": a(1, 2) = "Item Description": a(1, 3) = "Size"
    For i = 0 To AL.Count - 1
        a(1, i + 4) = txt & AL(i)
    Next
    For Each e In .keys
        w = .Item(e)
        For i = 1 To 3
            a(w(0), i) = w(2)(i - 1)
        Next
        For Each s In w(1).keys
            For ii = 5 To UBound(a, 2)
                a(w(0), AL.Indexof(s, 0) + 4) = w(1)(s)
            Next
        Next
    Next
End With
With Range("a1").CurrentRegion
    With .Offset(, .Columns.Count + 2)
        .CurrentRegion.Clear
        With .Resize(n, AL.Count + 3)
            .Value = a
            .ColumnWidth = 20
            .Columns.AutoFit
            .WrapText = True
            .Borders.Weight = 2
        End With
    End With
End With
Set AL = Nothing
End Sub

TestWithCode.xlsm

Jindon,
This works very well for the first 9 items, but stumbles when it gets to "Coke". The store numbers should correspond to the lines beginning with "Quantity". So, for the example "Coke", store 0 should correspond to 0, store 1 to 3, store 2 to 0, store 3 to 6, etc. I know it's strange. Also, it stops right after the coke example. If I had more lines, perhaps 25,000, how would I get this to work?
Thank you again! I appreciate your help!

Then you need to show me the exact result from the sample data.

as an option

Sub ert()
Dim x, y(), i&, j&, k&, n&, u&, id&, cnt&
Application.ScreenUpdating = False
x = Range("B3:R" & Cells(Rows.Count, 2).End(xlUp).Row + 1).Value
For i = 1 To UBound(x)
    ReDim y(1 To 1000): u = i
    y(1) = x(i, 1): y(2) = x(i, 2): y(3) = x(i, 3)

    Do Until Not IsNumeric(x(i + 1, 4))
        i = i + 1
    Loop
    n = i - u + 1
    For k = u To i
        For j = 4 To UBound(x, 2)
            If IsNumeric(x(k, j)) Then
                If Len(x(k, j)) Then id = x(k, j) + 4: y(id) = x(k + n, j)
            End If
        Next j
    Next k
    cnt = cnt + 1: Range("U3")(cnt, 1).Resize(, id).Value = y()
    i = i + n
Next i
Application.ScreenUpdating = True
End Sub