Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Convert tabular data to a matrix in Excel

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 23-04-2009
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

[TABLE="A"]Name Role
Mac Employee
Robert Employee
John Manager
John Supervisor
Mac Supervisor[/TABLE]

Desired format

[TABLE="B"]Name / Role Employee Supervisor Manager
Mac X X
John X X
Robert X [/TABLE]

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

Reply With Quote
  #2  
Old 23-04-2009
Member
 
Join Date: May 2008
Posts: 2,949
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
Reply With Quote
  #3  
Old 23-04-2009
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
   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
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Convert tabular data to a matrix in Excel"
Thread Thread Starter Forum Replies Last Post
How to convert monthly data into quarterly data in Microsoft Excel Glasney MS Office Support 4 04-01-2014 09:47 AM
Need help to convert monthly data to Quarterly in Microsoft Excel ELETTRA| MS Office Support 2 17-02-2012 01:06 PM
Tabular Data Control REDBULL Software Development 5 05-01-2010 07:59 AM
How to convert Excel file data into XML code Arif15 Software Development 3 20-10-2009 07:21 PM
Convert Excel data to HTML Table Chalina Software Development 2 04-08-2009 11:21 PM


All times are GMT +5.5. The time now is 09:30 PM.