Transpose Variable Amount of Columns in Range

**Ronjay24** · 03-04-2013, 06:41 PM

Hello,

I have a range of data that is organised in rows, from which I require a certain selection of those rows, based on a cell's value, to be copied to another worksheet and rearranged in a particular fashion. Attached is an example data set illustrating the original format of the data, and the desired end result after rearranging.

Refer to the attached worksheet for references made. Essentially, each row contains identification data (columns A:C), and a variable amount of result data (columns D:X, where X can be any number of columns from D). The result data are always paired in two columns named "i.d." and "str (MPa)" respectively. The subject data set will be the rows denoted with a "yes" in the 'results entered' column (C:C). This subject data set will then be copied to a new worksheet and rearranged.

The paired columns of result data are to be transposed. So that is, the record for "BR01" (row 3) has 3 column pairs (i.e. 3 results), and when rearranged, the 3 column pairs are transposed. The outcome is the data for "BR01" is spread over 3 rows, with the identification data (columns A:C) duplicated for each of the rows where a result data was transposed. The next row from the subject data set, "BR02" (row 4) will then be copied directly below the last row from "BR01" on the new sheet and transposed the amount corresponding to the number of paired result columns, (i.e. 1), and then so on for "BR03" and "BR04". The row for "BR05" would be excluded from the copy and rearrange as it has no results entered.

Key points to be considered:
- only rows with results entered (column C:C) as "yes" are to be included
- the number of paired results will vary
- process of copying and transposing to be looped for as many rows included in subject data set (could be in the hundreds)

Thank you in advance for your input.

Cheers,
Ronjay24

**Richard Buttrey** · 03-04-2013, 07:37 PM

Hi,

The following macro should work. It will put the results starting in column G of the Final Re Arranged sheet so that you can compare with your manual example. With the production environment data obviously change the G1 reference in the macro to A1 or wherever you want the table.

Sub ReOrderData()
    Dim lRow As Long, lColumns As Long, stComp As String, dt As Date, lCount As Long

    For lRow = 3 To Sheet1.Range("A1").CurrentRegion.Rows.Count
        If LCase(Sheet1.Range("A1").Cells(lRow, 3)) = "yes" Then
            stComp = Sheet1.Range("A1").Cells(lRow, 1)
            dt = Sheet1.Range("A1").Cells(lRow, 2)
            For lColumns = 1 To (Sheet1.Range("A1").Cells(lRow, Columns.Count).End(xlToLeft).Column - 3) / 2
                Sheet2.Range("G1").Cells(lRow + lCount, 1) = stComp
                Sheet2.Range("G1").Cells(lRow + lCount, 2) = dt
                Sheet2.Range("G1").Cells(lRow + lCount, 3) = "yes"
                Sheet2.Range("G1").Cells(lRow + lCount, 4) = Sheet1.Range("A1").Cells(lRow, lColumns * 2 + 2)
                Sheet2.Range("G1").Cells(lRow + lCount, 5) = Sheet1.Range("A1").Cells(lRow, lColumns * 2 + 3)

                lCount = lCount + 1
            Next lColumns

        End If
        lCount = lCount - 1
    Next lRow


End Sub