Selecting Cells Based on Criteria, then Relocate Special (Transpose)

Question

I was wondering if anyone could help me with the following problem. I have a matrix in a sheet like that:

  __   1w  |  2w  |  ..  |  25w

  a | 5,6  |  4,5 |  ..  |  12

  b | 2,4  | 11,2 |  ..  |  34,45

  : | :::  |  ::: |  ::  |  ::

  z | 3,3  |  1,5 |  ..  |  24,91

I would like to transpose the rows and columns in a special way so they remained as follows on a new sheet:

       1w  |   a  |  5,6
       2w  |   a  |  4,5   
       ..  |   .  |  ...
       25w |   a  |  12
       1w  |   b  |  2,4
       2w  |   b  |  11,2
       ..  |   .  |  ...
       25w |   b  |  34,45
       ..  |   .  |  ...
       ..  |   .  |  ...
       1w  |   z  |  3,3
       2w  |   z  |  1,5
       ..  |   .  |  ...
       25w |   z  |  24,91

I can do this by hand, but it will take a very long time because i have a lot of data. Is there anyway to automate it?

score 0 · Accepted Answer · answered Mar 18 '16 at 15:13

Flattening a matrix can be accomplished with nested looping or even SQL cross joins.

Sub flipShow()
    Dim a As Long, b As Long, vTMPs As Variant, vVALs As Variant

    With Worksheets("Sheet1")
        With .Cells(1, 1).CurrentRegion
            vTMPs = .Value2
            ReDim vVALs(1 To (UBound(vTMPs, 1) - 1) * (UBound(vTMPs, 2) - 1), 1 To 3)
        End With
    End With

    For a = LBound(vTMPs, 1) + 1 To UBound(vTMPs, 1)
        For b = LBound(vTMPs, 2) + 1 To UBound(vTMPs, 2)
            Debug.Print (b - 1) + ((a - 2) * UBound(vTMPs, 2))
            vVALs((b - 1) + ((a - 2) * (UBound(vTMPs, 2) - 1)), 1) = vTMPs(1, b)
            vVALs((b - 1) + ((a - 2) * (UBound(vTMPs, 2) - 1)), 2) = vTMPs(a, 1)
            vVALs((b - 1) + ((a - 2) * (UBound(vTMPs, 2) - 1)), 3) = vTMPs(a, b)
        Next b
    Next a

    With Worksheets("Sheet2")
        .Cells.Clear
        .Cells(1, 1).Resize(UBound(vVALs, 1), UBound(vVALs, 2)) = vVALs
    End With

End Sub

Sample results:

Thanks a lot!! It's what I needed. I need to learn VBA. Thanks m8 — Raul S, Mar 18 '16 at 16:08
This solution was derived from methods discussed at [Expanding column cells for each column cell](http://stackoverflow.com/questions/31472816/expanding-column-cells-for-each-column-cell/31594569#31594569). Additional methods and discussion on the theory behind them are available at that link. — , Mar 18 '16 at 19:16

Selecting Cells Based on Criteria, then Relocate Special (Transpose)

1 Answers1