Dynamically Convert Matrix to Columns

I am trying to create a way to create rows or individual records from a matrix.Attached is an example.  Using formulas or VBA, I'd like to be able to go from a matrix (3x3, for example) to 9 rows of 3 columns.  It's hard to explain without looking at the file, but if I had 3 students each with 3 values, I'd like to go from the table array to individuals records (3 for each student).  Can anyone lead me in the right direction.
BBluAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
Select the matrix and then run this code (I attached a working example)
Dim i As Integer
Dim j As Integer
Dim curRow As Integer

Application.ScreenUpdating = False

If Selection.Rows.Count > 1 And Selection.Columns.Count > 1 Then
  curRow = Selection.Row + Selection.Rows.Count + 1
  Range(Rows(curRow), Rows(curRow + (Selection.Rows.Count - 1) * (Selection.Columns.Count - 1))).Insert
  
  For i = 2 To Selection.Rows.Count
    For j = 2 To Selection.Columns.Count
      Range("A" & curRow).Value = Selection.Cells(i, 1)
      Range("B" & curRow).Value = Selection.Cells(1, j)
      Range("C" & curRow).Value = Selection.Cells(i, j)
      curRow = curRow + 1
    Next
  Next
Else
  MsgBox "No matrix selected."
End If

Application.ScreenUpdating = True

Open in new window

Converting-Matrix-to-Columns.xls
0
 
TommySzalapskiCommented:
You didn't attach anything. And it is hard to see what you mean. Do you just want to transpose the matrix? If so just use copy and paste special->transpose.
0
 
BBluAuthor Commented:
Oops.  Sorry.  Attached is the spreadsheet.
Converting-Matrix-to-Columns.xlsx
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
BBluAuthor Commented:
AWESOME, Tommy!  I guess there is no way to do it with formulas, huh?
0
 
TommySzalapskiConnect With a Mentor Commented:
Actually you can, but it's a bit more complicated. Here is a sheet showing how.
It's counting the number of rows and columns using row 1 and column A so either add nothing to them or hard code the number of rows and columns.
Matrix-to-Columns-formula.xls
0
 
BBluAuthor Commented:
Wow!  Perfect!  Thanks, Tommy.  I hope you are in here more often.  Great insight!
0
 
BBluAuthor Commented:
Just what I needed, much faster than I could have ever hoped!  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.