# Thread: Convert tabular data to a matrix in Excel

1. Member
Join Date
Apr 2009
Posts
1

## 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.

2. Member
Join Date
May 2008
Posts
2,945

## Re: Convert tabular data to a matrix in Excel

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

3. Member
Join Date
May 2008
Posts
685

## Re: Convert tabular data to a matrix in Excel

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
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```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•