Results 1 to 3 of 3

Thread: Convert tabular data to a matrix in Excel

  1. #1
    Join Date
    Apr 2009

    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. #2
    Join Date
    May 2008

    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:

  3. #3
    Join Date
    May 2008

    Re: Convert tabular data to a matrix in Excel

    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

Similar Threads

  1. Replies: 4
    Last Post: 04-01-2014, 09:47 AM
  2. Replies: 2
    Last Post: 17-02-2012, 01:06 PM
  3. Tabular Data Control
    By REDBULL in forum Software Development
    Replies: 5
    Last Post: 05-01-2010, 07:59 AM
  4. How to convert Excel file data into XML code
    By Arif15 in forum Software Development
    Replies: 3
    Last Post: 20-10-2009, 07:21 PM
  5. Convert Excel data to HTML Table
    By Chalina in forum Software Development
    Replies: 2
    Last Post: 04-08-2009, 11:21 PM

Posting Permissions

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