Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Dynamically Convert Matrix to Columns

Posted on 2010-11-22
Medium Priority
716 Views
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.
0
Question by:BBlu
• 4
• 3

LVL 37

Expert Comment

ID: 34193074
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

Author Comment

ID: 34193142
Oops.  Sorry.  Attached is the spreadsheet.
Converting-Matrix-to-Columns.xlsx
0

LVL 37

Accepted Solution

TommySzalapski earned 2000 total points
ID: 34193347
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
``````
Converting-Matrix-to-Columns.xls
0

Author Comment

ID: 34193381
AWESOME, Tommy!  I guess there is no way to do it with formulas, huh?
0

LVL 37

Assisted Solution

TommySzalapski earned 2000 total points
ID: 34193657
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

Author Comment

ID: 34194336
Wow!  Perfect!  Thanks, Tommy.  I hope you are in here more often.  Great insight!
0

Author Closing Comment

ID: 34194344
Just what I needed, much faster than I could have ever hoped!  Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month15 days, 2 hours left to enroll