Autogenerating a table from manually entered data

[nohlja]

Hello. I am unsure the best way to ask this question, but I have a data set or table that I manually fill in with specific quantities. In order to use this data, I need the data to be reformatted/reorganized into a different format to be used in a different application. I would like this to be automated, but I have been unable to find a lookup or filter formula that can automate the generation of this table.

I have attached an example spreadsheet. The information in Columns A-G is manually entered, but I would like the information in Columns J-M to auto populate.

I appreciate any insight any of you can provide.

Thanks!

[bebo021999]

VBA solution.
this code is put in sheet event, to trigger any change in range format 1, to update in range format 2
(Right click on tab's name, view code, then paste below code into

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr&, i&, j&, k&, rng, res(1 To 100000, 1 To 4)
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(Target, Range("A2:G" & lr)) Is Nothing Then Exit Sub
rng = Range("A2:G" & lr)
For j = 3 To UBound(rng, 2)
    For i = 2 To UBound(rng)
        If rng(i, j) <> "" Then
            k = k + 1
            res(k, 1) = rng(1, j): res(k, 2) = rng(i, 1)
            res(k, 3) = rng(i, 2): res(k, 4) = rng(i, j)
        End If
    Next
Next
Range("J3:M100000").ClearContents
Range("J3").Resize(k, 4).Value = res
End Sub 

[Czeslaw]

Power Query

l

et
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Item", "Item Description"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Attribute", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Attribute", "Date"}, {"Value", "Quantity"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Item", "Item Description", "Quantity"})
in
    #"Reordered Columns"

[windknife]

Clean all expected results.

=LET(
   a,FILTER(A3:G100,A3:A100<>""),
   b,A2:G2,
   REDUCE({"Date","Item","Item Description","Quantity"},SEQUENCE(COLUMNS(C2:G2),,3),LAMBDA(x,y,LET(c,TAKE(FILTER(a,INDEX(a,,y)<>""),,2),VSTACK(x,HSTACK(EXPAND(INDEX(b,1,y),ROWS(c),,INDEX(b,1,y)),c,FILTER(INDEX(a,,y),INDEX(a,,y)<>""))))))
)