Copying parts of a column and transposing them into rows

sheetspread

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

Some videos you may like

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
Joined
Feb 16, 2002
Messages
1,592
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
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
Yep, it worked

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

sheetspread

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

Watch MrExcel Video

Forum statistics

Threads
1,118,321
Messages
5,571,538
Members
412,402
Latest member
xam99
Top