Convert tabular data to a matrix in Excel

Hello experts,

I need your help to convert the data in a tabular format to that of a matrix as mentioned below.

Existing format


Desired format


Tried using various lookup,index,offset functions but had no luck!! Appreciate your early responses on the same.. Thanks.

Use a PivotTable to summarize, analyze, explore, and present data. A PivotTable report is an interactive way to quickly summarize large amounts of data. For assistance on how to create a PivotTable refer this: http://office.microsoft.com/en-us/ex...CH101693961033

Try this macro:

Code:

---

Public Sub TransformMatrix()

  Dim Row As Long
  Dim Column As Long
  Dim TargetRow As Long
  Dim TargetWorksheet As Worksheet
  Dim SourceWorksheet As Worksheet
 
  Set SourceWorksheet = ActiveSheet
  Set TargetWorksheet = Worksheets.Add
  TargetWorksheet.[A1:D1] = Array("Client", "Sales", "SalesValue", "Comment")
  TargetRow = 2
  With SourceWorksheet
      For Row = 4 To .Rows.Count
        If Len(.Cells(Row, "A")) = 0 Then Exit Sub
        For Column = 3 To 28
            If Len(.Cells(Row, Column)) > 0 Then
              TargetWorksheet.Cells(TargetRow, "A") = .Cells(Row, "A")
              TargetWorksheet.Cells(TargetRow, "B") = .Cells(1, Column)
              TargetWorksheet.Cells(TargetRow, "C") = .Cells(Row, Column)
              If Not .Cells(Row, Column).Comment Is Nothing Then
                  TargetWorksheet.Cells(TargetRow, "D") = .Cells(Row, Column).Comment.Text
                  If InStr(TargetWorksheet.Cells(TargetRow, "D"), ":") > 0 Then
                    TargetWorksheet.Cells(TargetRow, "D") = Mid(TargetWorksheet.Cells(TargetRow, "D"), InStr(TargetWorksheet.Cells(TargetRow, "D"), ":") + 1)
                  End If
                  TargetWorksheet.Cells(TargetRow, "D") = Replace(TargetWorksheet.Cells(TargetRow, "D"), vbLf, vbNullString)
              End If
              TargetWorksheet.Cells(TargetRow, "C") = .Cells(Row, Column)
              TargetRow = TargetRow + 1
            End If
        Next Column
      Next Row
  End With

End Sub

---