Search code examples
excelaccelerometertranspose

Transpose multiple cells from multiple rows to a single row in Excel

I have a large data set with groups of five sets of x, y, z values. It is currently set up as a single row with each value in its own cell for each interval within a group.

Example Data

enter image description here

I need to transpose every value (x, y, z) in rows by group.

Desired Format

enter image description here

The closest thing I have been able to find is a function to transpose values to a single column;

=INDEX($A$2:$C$6,1+INT((ROWS(E$2:$E2)-1)/COLUMNS($A$2:$C$6)),1+MOD(ROWS(E$2:$E2)-1,COLUMNS($A$2:$C$6))))

Ideally I would be able to AutoFill a function to create rows by group for the entire data set. Open to VBA script or R code if there is an easier way.

Solution

  • How about this. It puts the transposed rows into the 2nd sheet (I hard-coded the names "Sheet1" and "Sheet2" for simplicity but be sure to change these if they don't match). Also, I hard-coded the last line #, but if you want the code to determine the last row for you, you could use ActiveSheet.UsedRange.Rows.Count as described here.

    Sub TransposeRows()

Dim sourceRowPtr, destRowPtr, sourceColPtr, destColPtr, lastRow

'********
'set this to the end
lastRow = 500
'********

sourceRowPtr = 2
destRowPtr = 1
sourceColPtr = 1

While sourceRowPtr <= lastRow
    For destColPtr = 1 To 15
        Worksheets("Sheet2").Cells(destRowPtr, destColPtr).Value = Worksheets("Sheet1").Cells(sourceRowPtr, sourceColPtr).Value
        sourceColPtr = sourceColPtr + 1

        If sourceColPtr = 4 Then
            sourceColPtr = 1
            sourceRowPtr = sourceRowPtr + 1
        End If

    Next destColPtr

    destRowPtr = destRowPtr + 1
Wend


End Sub