Tricky Transpose
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. |
Tricky Transpose
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 -----Original Message----- 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. . |
Tricky Transpose
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 -----Original Message----- 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. . |
All times are GMT +1. The time now is 03:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com