Convert tabular data to a matrix in Excel

[Rookieinexcel]

Sponsored Links

Hello experts,

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

Existing format

No code has to be inserted here.

Desired format

No code has to be inserted here.

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

[Shen]

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

[fellah]

Try this macro:

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