|I am creating a table with laboratory data from groundwater monitoring wells (MW). The data is exported from an Access query into Excel. I currently have one column with over 545 data points to transpose. So being able to search down the column 21 rows at a time and transposing into individual rows of 21 columns by hand can be time consuming. |
I need to:
Start in column E2 > Select data from E2 to E22 (21 data points) > transpose E2:E22 to F2:Z2 (21 data points into one row)> Select data from E23 to E43 (the NEXT 21 data points)>transpose E23:E43 to F3:Z3 (21 data points into a SECOND row)> and so on until end of data in column.
To add a bit of complexity, I need to add a blank row between each transposed row.
Example of layout:
Col/Row Data Point
E5 < 1.0
E6 < 1.0
...and so on... to E22
E23 < 1.0
E25 < 1.0
E26 < 1.0
E27 < 4.0
E28 < 1.0
...and so on... to E43
The output would look like this:
Col-F Col-G Col-H Col-I Col-J Col-K
Row 3 (MW-1) 17 160 21 < 1.0 < 1.0 1.3 ...and so on to Z3...
Row 4 (MW-2) < 1.0 2 < 1.0 < 1.0 < 4.0 < 1.0 ...and so on to Z4...
...and so on to include 25 monitoring wells.