Best Way to Transform Data into Desired Format

Try this macro

Sub Test()
Sheets("Original").Activate
Sheets("Desired").Cells.Clear
Range("A1:F1").Copy Destination:=Sheets("Desired").Range("A1:F1")
TargetRow = 1
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(N, 1) <> Cells(N - 1, 1) Or Cells(N, 2) <> Cells(N - 1, 2) Or Cells(N, 3) <> Cells(N - 1, 3) Or Cells(N, 4) <> Cells(N - 1, 4) Or Cells(N, 5) <> Cells(N - 1, 5) Or Cells(N, 6) <> Cells(N - 1, 6) Then
        TargetRow = TargetRow + 1
        Range(Cells(N, 1), Cells(N, 6)).Copy Destination:=Sheets("Desired").Cells(TargetRow, 1)
    End If
    If WorksheetFunction.CountIf(Sheets("Desired").Rows(1), Cells(N, 7)) = 0 Then
        Sheets("Desired").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(N, 7)
    End If
    TargetColumn = Sheets("Desired").Rows(1).Find(Cells(N, 7), , xlValues, xlWhole).Column
    Sheets("Desired").Cells(TargetRow, TargetColumn) = Cells(N, 8)
Next N
End Sub

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

@AnitPivotTable

Still with an pivot table.

See the attached file.

And yet another:

Sub APT(): Dim i As Long, j As Long, k As Long, wo As Worksheet, wd As Worksheet
Set wo = Sheets("Original"): Set wd = Sheets("Desired"): j = 2: k = 2
GetHeads: wo.Rows(1).copy wd.Range("A1")
i = 2: Do Until wo.Cells(i + 1, 7) < wo.Cells(i, 7): i = i + 1: Loop
wo.Cells(2, 7).Resize(i - 1, 1).copy: wd.Cells(1, 7).PasteSpecial Transpose:=True
LoadDetail: Do Until wo.Cells(j, 1) = ""
wd.Cells(k, 1).Resize(1, 6).Value = wo.Cells(j, 1).Resize(1, 6).Value
wo.Cells(j, 8).Resize(i - 1, 1).copy: wd.Cells(k, 7).PasteSpecial Transpose:=True
            j = j + i - 1: k = k + 1: Loop: End Sub