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

x
?
Solved

Convert a matrix of data into columnar data

Posted on 2012-03-29
11
Medium Priority
?
409 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 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
Technology Partners: 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: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
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 how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

579 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