Solved

Dynamically Convert Matrix to Columns

Posted on 2010-11-22
7
703 Views
Last Modified: 2012-05-10
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
Comment
Question by:BBlu
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
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

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

Accepted Solution

by:
TommySzalapski earned 500 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

Open in new window

Converting-Matrix-to-Columns.xls
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

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

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 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

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

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question