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
Bookmarks