Excel 2007 Transpose macro

March 4, 2009 at 10:20:24
Specs: Windows XP
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
E2 17
E3 160
E4 21
E5 < 1.0
E6 < 1.0
E7 1.3

...and so on... to E22

E23 < 1.0
E24 2
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.

Thanks!

Russell

WinXp
Excel 2007


See More: Excel 2007 Transpose macro

Report •


#1
March 4, 2009 at 10:52:43
Sorry don't have 2007, but here a some links in case you need them.

http://office.microsoft.com/en-us/h...

http://office.microsoft.com/en-us/e...

http://spreadsheets.about.com/od/ex...

It's a fairly straight forward operation in my version, don't know what MS has done "improve" it in 2007.

MIKE


Report •

#2
March 4, 2009 at 11:14:43
Thanks Mike.

I'll check out the those websites to see if they work for me.

Russel


Report •

Related Solutions


Ask Question