Solved

Convert a matrix of data into columnar data

Posted on 2012-03-29
11
395 Views
Last Modified: 2012-03-29
I have a matrix of data with several columns.  I need to convert this into individual records/rows.  For example, if along the top I have x, y, and z as the fields and below each of them I have all of the results, I need to have a two column list that reads (first row/record) x | 1, (second row/record) x | 23, x | 24 ........ y | 45, y | 17 ....... z | 34, z | 11 etc.

Please see the attached for an example of a matrix and the desired output.

I was thinking of creating a subroutine that took as its inputs a) the entire matrix/array and b) a cell to put the first record in the new output columns.  I would then use some loop (maybe a 'for each in' loop) to go through the matrix and write the two columns.

Does anyone have an idea of how to do this?
converting-matrix-data.xlsx
0
Comment
Question by:BBlu
  • 7
  • 4
11 Comments
 

Author Comment

by:BBlu
ID: 37785057
I just realized that there probably isn't a way I could feed the subroutine 2 arguments.  I would be okay with creating a new worksheet, then writing the columns.
0
 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 37785097
Here is a macro that will normalize your data into a two column list. It assumes that you have selected a cell in the current region of the source data, and will ask you for the top left cell in the target list. You will need to provide your own header labels for the target list if you are making a PivotTable.

Sub Normalizer()
Dim cel As Range, col As Range, rg As Range, rgg As Range
Dim i As Long, j As Long, n As Long, nCols As Long
Set rg = ActiveCell.CurrentRegion
On Error Resume Next
Set cel = Application.InputBox("Please select the top left cell in your target table", Type:=8)
On Error GoTo 0
If cel Is Nothing Then Exit Sub

Application.ScreenUpdating = False
n = rg.Rows.Count
nCols = rg.Columns.Count
Set rgg = rg.Offset(1, 0).Resize(n - 1)
For j = 1 To nCols
    cel.Offset(i, 0).Resize(n - 1, 1).Value = rg.Cells(1, j).Value
    cel.Offset(i, 1).Resize(n - 1, 1).Value = rgg.Columns(j).Value
    i = i + n - 1
Next
End Sub

Open in new window


Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 37785110
If you want to call a sub containing the preceding logic from within VBA, then you could pass it the range parameters for rg and cel directly.

Sub Normalizer2(rg As Range, cel As Range)
'rg is the range of source data, including header labels
'cel is the top left cell of the destination table (two columns). You will need to put your own header labels above this cell.
Dim col As Range, rgg As Range
Dim i As Long, j As Long, n As Long, nCols As Long
Application.ScreenUpdating = False
n = rg.Rows.Count
nCols = rg.Columns.Count
Set rgg = rg.Offset(1, 0).Resize(n - 1)
For j = 1 To nCols
    cel.Offset(i, 0).Resize(n - 1, 1).Value = rg.Cells(1, j).Value
    cel.Offset(i, 1).Resize(n - 1, 1).Value = rgg.Columns(j).Value
    i = i + n - 1
Next
End Sub

Open in new window

0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:BBlu
ID: 37785152
Thanks, Brad.  I'm trying to use the first comment, but I'm experiencing issues.  Would I select the first (upper left) value, which would be the first header ("x") or the first value under "x" which is 6?
0
 
LVL 81

Expert Comment

by:byundt
ID: 37785158
To run my first macro with your sample data:
1) Select any cell in the range A2:C23
2) Run the macro
3) The macro then asks you to select cell G3. It will put the x, y or z in column G and the values in column H.

The CurrentRegion property of a cell is the range of contiguous cells containing adjacent values. It is a rectangular range containing your data and surrounded by blank rows at top and bottom and blank columns at left and right. The edges of the worksheet also count as blank rows or columns for this purpose.
0
 

Author Comment

by:BBlu
ID: 37785167
Oh, gotcha.  That's what the "current region" portion of your code does.  It captures the region of contiguous cells, right?
0
 

Author Comment

by:BBlu
ID: 37785171
Never mind..I was so excited that I didn't read your entire comment.  I just tried the code and it worked..I didn't see that you'd already said what I asked.
0
 
LVL 81

Expert Comment

by:byundt
ID: 37785180
Correct. It's a pretty neat concept, isn't it?

You can experiment with Current Region by selecting a cell on the worksheet and then hitting F5, clicking the Special... button and choosing Current Region.
0
 

Author Comment

by:BBlu
ID: 37785182
Thanks, Brad.  It sounds like calling the values for the first subroutine from a second doesn't add much value.  I was confused by what functions can do versus subroutines.  Your code worked perfectly.  Thanks, again!
0
 

Author Comment

by:BBlu
ID: 37785197
Very neat concept.  Thanks, man.  Awesome!
0
 

Author Closing Comment

by:BBlu
ID: 37785198
Awesome!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 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