PDA

View Full Version : Solved: Turning an m*n matrices into m*n rows



NukedWhale
01-15-2009, 03:47 PM
I have a 180*180 matrices. My rows and columns have headers.

Is there an easy way (built in excel functions, applications or VBA) to transform my matrices into 3 columns and 32400 rows?

I would like my 3 columns to contain in no particular order
1. Column Header
2. Row Header
3. Score

If not, I have several looping functions which I should be able use to look up all of the results.

Thanks!

Bob Phillips
01-15-2009, 04:30 PM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("A:B").Insert
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 3 To LastCol

.Cells((i - 3) * LastRow + 1, "A").Value = 1
.Cells((i - 3) * LastRow + 2, "A").Value = 2
.Cells((i - 3) * LastRow + 1, "A").Resize(2).AutoFill .Cells((i - 3) * LastRow + 1, "A").Resize(LastRow)
.Cells((i - 3) * LastRow + 1, "B").Resize(LastRow).Value = i - 2
.Cells(1, i).Resize(LastRow).Copy .Cells((i - 3) * LastRow + 1, "C")
Next i
.Columns(4).Resize(, LastCol - 3).Delete
End With
End Sub

NukedWhale
01-18-2009, 09:22 PM
Thanks for the post xld, I didn't have any luck with your macro, perhaps I was using it incorrectly.

I've uploaded an example document.

Column A and row 1 have my header rows.

Bob Phillips
01-19-2009, 01:42 AM
Here is a slightly amended version now that I have seen the data



Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("B:C").Insert
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 4 To LastCol

.Cells((LastRow - 1) * (i - 4) + 2, "A").Resize(LastRow - 1).Value = .Cells(2, "A")
.Cells((LastRow - 1) * (i - 4) + 2, "B").Resize(LastRow - 1).Value = .Cells(1, i)
.Cells(2, i).Resize(LastRow - 1).Copy .Cells((LastRow - 1) * (i - 4) + 2, "C")
Next i
.Columns(4).Resize(, LastCol - 3).Delete
.Rows(1).Delete
End With
End Sub

NukedWhale
01-19-2009, 01:08 PM
The labels don't appear to be generating correctly.

Does your code have any comments? I'm not that versed in VBA, but I would love to be able to help debug and give something back. If I know what the lines of code are supposed to be doing, I think I'd be able to help.

I hope I don't sound ungrateful, your help is always appreciated xld!

Bob Phillips
01-19-2009, 01:12 PM
I'll tell you what, you tak say a 10 by 10 chunk out of that matrix and show me what you expect to get, and I will comment that code (believe me that is some offer, I read code better than I read comments).

Aussiebear
01-19-2009, 05:09 PM
Bob, I've had a quick look at the file and I think the problem is that the "Labels" the OP is referring to doesn't increment correctly. Have a quick look at my attached file to see the layout issue.

NukedWhale
01-19-2009, 09:06 PM
Wow, thanks Aussiebear. Your example captured my issue correctly.

Please, pardon my poor communication xld.

NukedWhale
01-19-2009, 11:15 PM
Holy cow, I think I found the fix for it.

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim LastCol As Long

'Active sheet selected
With ActiveSheet

'Count the number of rows in column A, how does it know when it's stopped? End function?

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'Insert Columns B and C
.Columns("B:C").Insert

'Count the number of columns in row 1, how does it know when it's stopped? End function?

LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

For i = 4 To LastCol
'This row is the one I updated
.Cells(2, 1).Resize(LastRow - 1).Copy .Cells((LastRow - 1) * (i - 4) + 2, "A")
.Cells((LastRow - 1) * (i - 4) + 2, "B").Resize(LastRow - 1).Value = .Cells(1, i)
.Cells(2, i).Resize(LastRow - 1).Copy .Cells((LastRow - 1) * (i - 4) + 2, "C")

Next i
.Columns(4).Resize(, LastCol - 3).Delete
.Rows(1).Delete
End With
End Sub

Aussiebear
01-20-2009, 02:51 AM
Well there you go then. All it takes is a bit of thought about the code that had been provided.

NukedWhale
01-20-2009, 09:21 AM
Thanks xld and aussiebear. Marking this solved, moving questions about individual lines of code for my understanding to new threads.