Solved

Dynamically Convert Matrix to Columns

Posted on 2010-11-22
7
706 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

696 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