Copying parts of a column and transposing them into rows

Well-known Member
Say I have three groups of 10 numbers in a column:

731
1851
397
157
760
457
207
1238
1671
672

1208
1307
1720
877
255
1769
1614
1276
236
304

799
184
1791
1118
1111
757
1328
1072
1572
257

And I want to copy and transpose them into 3 rows at once without copying/pasting each column special:

731 1851 397 157 760 457 207 1238 1671 672
1208 1307 1720 877 255 1769 1614 1276 236 304
799 184 1791 1118 1111 757 1328 1072 1572 257

Can this be done?

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

Derek

Well-known Member
Hi there

If your column of data is fixed (ie the blocks of numbers always start at the same cells) you can transpose by formula.

Say your blocks of data start at A1, A15 and A27 then for the first block use this formula in the first transpose cell (and then scroll right)
=OFFSET(\$A\$1, COLUMN(A1)-1,0)
Change \$A\$1 to \$A\$15 for the second row
Change \$A\$1 to \$A\$27 for the third row

Regards
Derek

Well-known Member
Yep, it worked

I read about offset last night while searching for a solution. Glad someone could show me how to apply it here.

Well-known Member
Thanks Peter

I'll study that one also for future use. It is more complex than the offset solution, but I may actually need it for a larger table that occasionally has to be referred to.

Replies
1
Views
127
Replies
6
Views
126
Replies
1
Views
137
Replies
19
Views
1K
Replies
1
Views
281