Solved

Convert a matrix of data into columnar data

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 97
change VBA to show result in cells rather than debug.print 4 34
Sum iF  based on a null cell 11 29
Dynamic Filter ? 4 18
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

910 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

21 Experts available now in Live!

Get 1:1 Help Now