Tricky Transpose

[Jan Cairns]

Sheet2 has a set of results columns B..E but the number of
columns could increase or decrease depending on number of
tests applied per sample, as could the number of rows
depending on the number of samples being processed.

I need a macro that will take into account the variable
number of columns and rows that could be on sheet2, then
copy the data onto sheet 1, row by row. Sheet1 can only
contain the exact results and no formulas and the
information is displayed in a different format.

Note the Sample Name is required to show against each
result (sheet1) as there are number of results in sheet2.
Likewise the DET in sheet1 is the contents of row 3 on
sheet2 above each result in the same column.

Example of Sheet 2

Sample Name Amount Amount Amount
ppm ppm ppm
glucose fructose sucrose
ECD_1 ECD_1 ECD_1
AQC 39.5076 37.4709 49.7239
70089213 29.4201 4.5945 3.4349
70089216 33.8177 29.1102 40.4859
70090148 33.6391 31.6154 44.4277



Example of required output on sheet 1
A B C
SAMPNUM RESULT DET
AQC 39.5076 IGlu_dx
AQC 37.4709 Ifru_dx
AQC 49.7239 Isuc_dx
70089213 29.4201 IGlu_dx
70089213 4.5945 Ifru_dx
70089213 3.4349 Isuc_dx
70089216 33.8177 IGlu_dx
70089216 29.1102 Ifru_dx
70089216 40.4859 Isuc_dx
70090148 33.6391 IGlu_dx
70090148 31.6154 Ifru_dx
70090148 44.4277 Isuc_dx

I know what I want the macro to do but I just don't know
where to start.

 

[Patrick Molloy]

copy the following code into a standard module:-

Option Explicit
Sub results()
Dim output_row As Long
Dim source_col As Long
Dim source_LastCol As Long
Dim source_row As Long
Dim source_lastrow As Long
Dim sample As String
'initialise target
Sheet1.Cells.Clear
output_row = 1
With Sheet2
source_LastCol = _
.Range("IV1").End(xlToLeft).Column
source_lastrow = _
.Range("A65000").End(xlUp).Row
For source_row = 2 To source_lastrow
sample = .Cells(source_row, 1)
For source_col = 2 To source_LastCol

output_row = output_row + 1
Sheet1.Cells(output_row, 1) = sample
Sheet1.Cells(output_row, 2) = _
.Cells(source_row, source_col)
Sheet1.Cells(output_row, 3) = _
.Cells(1, source_col)

Next source_col

Next source_row

End With

End Sub

it assumes that your data starts on sheet2 row 2

Patrick Molloy
Microsoft Excel MVP

workbook available on request

 

[Patrick Molloy]

copy the following code into a standard module:-

Option Explicit
Sub results()
Dim output_row As Long
Dim source_col As Long
Dim source_LastCol As Long
Dim source_row As Long
Dim source_lastrow As Long
Dim sample As String
'initialise target
Sheet1.Cells.Clear
output_row = 1
With Sheet2
source_LastCol = _
.Range("IV1").End(xlToLeft).Column
source_lastrow = _
.Range("A65000").End(xlUp).Row
For source_row = 2 To source_lastrow
sample = .Cells(source_row, 1)
For source_col = 2 To source_LastCol

output_row = output_row + 1
Sheet1.Cells(output_row, 1) = sample
Sheet1.Cells(output_row, 2) = _
.Cells(source_row, source_col)
Sheet1.Cells(output_row, 3) = _
.Cells(1, source_col)

Next source_col

Next source_row

End With

End Sub

it assumes that your data starts on sheet2 row 2

Patrick Molloy
Microsoft Excel MVP

workbook available on request