writing a macro to transpose 3 columns into 1 row

Question

I have data as below which is 3 columns I need to transpose into 1 row. The row position needs to be changeable as I move down the specimens. I'm not at all familiar with Excel (am using 2013). Any help would be wonderful. I need to do this hundreds of times and cut and paste then transpose takes too long. Any ideas?

-5.13E+01   -1.99E+02   -1.53E+00
-5.86E+01   -2.12E+02    2.05E+00
-6.61E+01   -2.21E+02    7.45E+00
-6.67E+01   -2.34E+02    1.29E+01
-6.38E+01   -2.46E+02    1.57E+01
-3.90E+01   -2.56E+02    2.54E+01
-1.51E+01   -2.44E+02    2.33E+01
-1.02E+00   -2.31E+02    1.23E+01
-8.72E-01   -2.21E+02    4.19E+00
-1.39E+00   -2.10E+02    1.42E+00
-5.88E+00   -2.00E+02   -1.41E+00
-1.08E+01   -1.89E+02   -1.52E+00
 6.15E+00   -2.27E+02   -2.09E+01
-3.11E+00   -2.25E+02   -1.97E+01
-1.96E+01   -2.29E+02   -1.00E+01
-2.52E+01   -2.32E+02   -4.73E+00
-4.17E+01   -2.35E+02    2.48E+00
-4.44E+01   -2.46E+02    6.48E+00
-4.22E+01   -2.60E+02    1.02E+01
-5.32E+01   -2.30E+02    2.18E+00

Cheers

Yes. The columns need to form a row in the same order they are in as above. They're measurements with variable labels along the first row. — user2767719, Sep 11 '13 at 08:59

score 0 · Answer 1 · answered Sep 12 '13 at 00:36

I think this works, but I'd recommend checking!:

=IF(COLUMN()<24,INDEX($A$2:$C$21,COLUMN()-3,1),IF(COLUMN()<44,INDEX($A$2:$C$21,COLUMN()-23,2),INDEX($A$2:$C$21,COLUMN()-43,3)))

in D1 and copied across, assuming -5.13E+01 is in A2 and so forth.

(Even if it does, it probably is far from ideal though, just the best offer you've had so far.)

writing a macro to transpose 3 columns into 1 row

1 Answers1

Linked