Copying parts of a column and transposing them into rows

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,160
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
Yep, it worked

I read about offset last night while searching for a solution. Glad someone could show me how to apply it here.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top