A pivot table will do what you want.
This is a discussion on Converting Matrix Data to Vector and Vice Versa (Excel) within the Excel Questions forums, part of the Question Forums category; Hey, I was looking to try and find out how to convert a matrix to a vector in excel. I'm ...
Hey,
I was looking to try and find out how to convert a matrix to a vector in excel. I'm brand new to VBA so I'm not even close to at a point where I can make a program myself to do it but I need to figure out a way to do it for work.
It would also be nice to figure out a way to do the vice versa too.
Essentially I work with temperature data which I get in a format such as
Year Month Temperature
1946 1 2
1946 2 5
1946 3 3
1946 4 2
...etc...
I'd like to have it in this kind of format (matrix)
Year 1 2 3 4 5 6 ...etc... 12 (months in a year)
1946 2 5 3 2
The year part is not important really cause its easy to add in afterwards so really I just need to figure out how to do the grid of temperatures organized by months like shown above (still in order by year)
It would also be nice to learn how to do the vice versa i.e. switching from the gridded format back to the single line so I can do 12-month running means...
Thanks!
A pivot table will do what you want.
Hey, Thanks for the quick reply.
I don't think a pivot table will exactly suit my needs at this point. I have never used one before and in trying to use it (in response to your post) I did find it a bit more cumbersome than I was hoping. Plus it summarized many things by summing fields in which I would be looking for averages (because it is temperature data).
I think what I am more inclined to be looking for is either some VBA code or a macro which would allow for simply the creation of a grid from vector data And if possible to find a method to do the reverse also. It is something one can manually do but with 50-60 datasets I am working with, it is difficult and time consuming. Thank you for your post though.
Ex)
1
2
3
4
5
6
1 2
3 4
5 6
I'm not as familiar with Pivot Tables as some, but by right clicking on the form, you can change the SUM.
But if you want a dedicated VB routine, does the imported data have 12 months of each year?
Hello again,
ahh yes I noticed what you mean with the pivot table pertaining to switching between the different statistics. It might be helpful in other aspects with what I'm doing but probably not this part.
Pertaining to your question as to my data. I receive data with 12 months in a year.
Thank you for your help and time.
I think this will do what you want.
Code:Sub Macro1() Dim sourceRange As Range, destinationRange As Range Dim monthAddress As String, yearAddress As String Dim lookupVal As String, lookupVector As String, resultVector As String Dim i As Long, rowPointer As Long With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust Set sourceRange = Range(.Cells(2, 3), .Cells(.Rows.Count, 1).End(xlUp)) End With Set destinationRange = ThisWorkbook.Sheets("Sheet1").Range("G25"):Rem adjust Set destinationRange = destinationRange.Resize(1, 13) Rem make row of month headers destinationRange.Value = Array("Yr.\Mo.", 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) Rem make column of year headers rowPointer = 1 With sourceRange For i = 1 To .Rows.Count If .Cells(i, 1) <> .Cells(i + 1, 1) Then rowPointer = rowPointer + 1 destinationRange.Resize(1, 1).Cells(rowPointer, 1).Value = .Cells(i, 1).Value End If Next i End With Rem addresses for formula With destinationRange monthAddress = .Cells(1, 2).Address(True, False, xlR1C1, True, .Cells(2, 2)) yearAddress = .Cells(2, 1).Address(False, True, xlR1C1, True, .Cells(2, 2)) lookupVal = "(" & yearAddress & "+" & monthAddress & "/100)" End With With sourceRange lookupVector = .Columns(1).Address(True, True, xlR1C1, True) & "+(" & .Columns(2).Address(True, True, xlR1C1, True) & "/100)" resultVector = .Columns(3).Address(True, True, xlR1C1, True) End With Rem fill body with formula and make constant With destinationRange With .Offset(1, 1).Resize(rowPointer - 1, .Columns.Count - 1) .FormulaR1C1 = "=Lookup(" & lookupVal & "," & lookupVector & "," & resultVector & ")" .Value = .Value End With End With End Sub
Thank you very much. The vba script works perfectly and does exactly what it should. This has saved me a lot of time and I wish I had it earlier in the year when I did this sort of work manually for hours upon hours...
This is very much appreciated, thank you!
I was wondering if there is any way to do the opposite conversion. I receive the values initially as a vector and then I convert them to a matrix with your macro (greatly appreciated by the way) then I do some statistical analysis which comes out as a matrix of the same dimensions and then I have to do a 12 month running mean on the grid values. Therefore I imagine I'd have to convert it back to a vector or is there another way to do a running mean without having it as a vector?
What the macro does is extract the list of years, lay out the matrix and then fills the matrix with a formula.
The inverse can be done with formulas. This shows the reverse, with a smaller matrix.
The formula in H2 is =VLOOKUP(F2,$A$1:$D$4,MATCH(G2,$A$1:$D$1),FALSE)
which is dragged down.
A B C D E F G H 1 Year\Month 1 2 3 Year Month 2 1946 a b c 1946 1 a 3 1947 aa bb cc 1946 2 b 4 1946 3 c 5 1947 1 aa 6 1947 2 bb 7 1947 3 cc
Hello again,
Thank you for your help with the aforementioned problems. I'm not terribly familiar with formulas and I have had quite a bit of trouble implementing and understanding the formula you showed me there. I'm sure it is a matter of me being not an excel wizard unfortunately. Is there any perhaps easier way for a novice like myself?
Also I was wondering if there was any manner to calculate the offset between two corresponding series such as these?
Like this thread? Share it with others