Macro or other way to take 3 rows and transpose

**FrogWrangler** · 02-19-2011, 11:42 AM

Hi Everyone!
I am a frog biologist who is trying to figure out how to wrangle my data into a usable form. The output from the tests on samples I did gives 3 rows of data for each sample and the program I want to use the data in requires each sample to have all of the data on one row. I have been struggling with vlookup and offset trying to figure out how to do it, but I think it will better be done with a macro. But I am much better with frogs than programming. I hope someone here can help!

Here is what the column headers for the data looks like now (for each sample, there are 3 rows of the following and data begins at A2):
RunName; SampleName; Ct; Qty; DilutionFactor; Checked; OkaytoEnter; Rerun; Okayfor+/-; Rerunfor+/-; RerunwithDilution

Data in each row for a sample will be the same, except for the Ct and the Qty values. I just need to grab the first row of data and then add the Ct and Qty for the next two rows to it. It would have column headers like this:
RunName; SampleName; Ct1; Ct2; Ct3; Qty1; Qty2; Qty3; DilutionFactor; Checked; OkaytoEnter; Rerun; Okayfor+/-; Rerunfor+/-; RerunwithDilution

Any help with this would be greatly appreciated by myself and some little frogs!

**mrice** · 02-19-2011, 04:06 PM

Asusming that your source data is on Sheet1 and transformed data will go onto Sheet2, try the following.

Sub Test()
Sheets("Sheet1").Activate
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
    For P = 0 To 2
        TargetRow = ((N + 1) / 3) + 1
        For M = 1 To 11
            Select Case M
                Case 1 To 2
                    Sheets("Sheet2").Cells(TargetRow, M) = Sheets("Sheet1").Cells(N, M)
                Case Is = 3
                    Sheets("Sheet2").Cells(TargetRow, M + P) = Sheets("Sheet1").Cells(N + P, M)
                Case Is = 4
                    Sheets("Sheet2").Cells(TargetRow, M + P + 2) = Sheets("Sheet1").Cells(N + P, M)
                Case 5 To 11
                    Sheets("Sheet2").Cells(TargetRow, M + 4) = Sheets("Sheet1").Cells(N, M)
    
            End Select
        Next M
    Next P
Next N
End Sub