Solved

Convert a matrix of data into columnar data

Posted on 2012-03-29
11
391 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 80

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 80

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
 

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 80

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now