Results 1 to 9 of 9

Converting Matrix Data to Vector and Vice Versa (Excel)

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 ...

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    5

    Post Converting Matrix Data to Vector and Vice Versa (Excel)

    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!

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,940

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    A pivot table will do what you want.

  3. #3
    New Member
    Join Date
    Jun 2010
    Posts
    5

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,940

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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?

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    5

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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.

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,940

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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

  7. #7
    New Member
    Join Date
    Jun 2010
    Posts
    5

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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?

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,940

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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.









     ABCDEFGH
    1Year\Month123YearMonth
    21946abc19461a
    31947aabbcc19462b
    419463c
    519471aa
    619472bb
    719473cc

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    5

    Default Re: Converting Matrix Data to Vector and Vice Versa (Excel)

    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

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com