Transpose Row by Row
 

Does anyone have a shorter/simpler way of transposing more than 150 rows
of data that is in 5 columns into one column? The only catch or
difficulty is it has to be in the order of the rows vertically. See
below.
12 15 45 20 12
13 15 45 20 15
14 15 45 20 45
16 15 45 20 20
17 15 45 20 13
18 15 45 20 15
19 15 45 20 45
20 15 45 20 20
21 15 45 20 14
15
45
20
16
15
45
20
17
15
45
20
18
15
45
20

I did create a macro which is repetitive and was wondering if anyone
can help make is simpler this way it transpose more than 100 rows of
data into one column.
Range("A1:D1").Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone,SkipBlanks:=False ,
Transpose:=True
Application.CutCopyMode = False
Range("A2:D2").Select
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A3:D3").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A4:D4").Select
Selection.Copy
Range("F13").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A5:D5").Select
Selection.Copy
Range("F17").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A6:D6").Select
Selection.Copy
Range("F21").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("E1").Select
End Sub


--
walan
------------------------------------------------------------------------
walan's Profile: http://www.excelforum.com/member.php...o&userid=13528
View this thread: http://www.excelforum.com/showthread...hreadid=531240

Transpose Row by Row
 

Assume your data starts A1

=INDEX($A$1:$D$150,FLOOR(ROWS($A$1:A4)/4,1),MOD(ROWS($A$1:A4),4)+1)

copy down

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"walan" wrote in message
...

Does anyone have a shorter/simpler way of transposing more than 150 rows
of data that is in 5 columns into one column? The only catch or
difficulty is it has to be in the order of the rows vertically. See
below.
12 15 45 20 12
13 15 45 20 15
14 15 45 20 45
16 15 45 20 20
17 15 45 20 13
18 15 45 20 15
19 15 45 20 45
20 15 45 20 20
21 15 45 20 14
15
45
20
16
15
45
20
17
15
45
20
18
15
45
20

I did create a macro which is repetitive and was wondering if anyone
can help make is simpler this way it transpose more than 100 rows of
data into one column.
Range("A1:D1").Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone,SkipBlanks:=False ,
Transpose:=True
Application.CutCopyMode = False
Range("A2:D2").Select
Selection.Copy
Range("F5").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A3:D3").Select
Selection.Copy
Range("F9").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A4:D4").Select
Selection.Copy
Range("F13").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A5:D5").Select
Selection.Copy
Range("F17").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("A6:D6").Select
Selection.Copy
Range("F21").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range("E1").Select
End Sub


--
walan
------------------------------------------------------------------------
walan's Profile:
http://www.excelforum.com/member.php...o&userid=13528
View this thread: http://www.excelforum.com/showthread...hreadid=531240