Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Convert a matrix of data into columnar data

Posted on 2012-03-29
11
Medium Priority
?
405 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
[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
  • 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 1600 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

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!

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

636 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