Converting Matrix Data to Vector and Vice Versa (Excel)

[rway024]

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!

 

[mikerickson]

A pivot table will do what you want.

 

[rway024]

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

 

[mikerickson]

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?

 

[rway024]

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.

 

[mikerickson]

I think this will do what you want.

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

 

[rway024]

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?

 

[mikerickson]

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.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b><td width=25><b>H</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Year\Month<td align="right" bgcolor=#FFFFFF>1<td align="right" bgcolor=#FFFFFF>2<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF>Year<td align="left" bgcolor=#FFFFFF>Month<td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="right" bgcolor=#FFFFFF>1946<td align="left" bgcolor=#FFFFFF>a<td align="left" bgcolor=#FFFFFF>b<td align="left" bgcolor=#FFFFFF>c<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1946<td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF>a</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="right" bgcolor=#FFFFFF>1947<td align="left" bgcolor=#FFFFFF>aa<td align="left" bgcolor=#FFFFFF>bb<td align="left" bgcolor=#FFFFFF>cc<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1946<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>b</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1946<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF>c</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1947<td align="right" bgcolor=#FFFFFF>1<td align="left" bgcolor=#FFFFFF>aa</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1947<td align="right" bgcolor=#FFFFFF>2<td align="left" bgcolor=#FFFFFF>bb</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>1947<td align="right" bgcolor=#FFFFFF>3<td align="left" bgcolor=#FFFFFF>cc</tr>
</table>

 

[rway024]

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?