software

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.
1215452012
1315452015
1415452045
1615452020
1715452013
1815452015
1915452045
2015452020
2115452014
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(quot;A11quot;).Select
Selection.Copy
Range(quot;F1quot;).Select
Selection.PasteSpecial Paste:=xlAll,
Operation:=xlNone,SkipBlanks:=False ,
Transpose:=True
Application.CutCopyMode = False
Range(quot;A22quot;).Select
Selection.Copy
Range(quot;F5quot;).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range(quot;A33quot;).Select
Selection.Copy
Range(quot;F9quot;).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range(quot;A44quot;).Select
Selection.Copy
Range(quot;F13quot;).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range(quot;A55quot;).Select
Selection.Copy
Range(quot;F17quot;).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range(quot;A66quot;).Select
Selection.Copy
Range(quot;F21quot;).Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
Range(quot;E1quot;).Select
End Sub--
walan
------------------------------------------------------------------------
walan's Profile: www.excelforum.com/member.php...oamp;userid=13528
View this thread: www.excelforum.com/showthread...hreadid=531240Assume 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

quot;walanquot; gt; wrote in message
...
gt;
gt; Does anyone have a shorter/simpler way of transposing more than 150 rows
gt; of data that is in 5 columns into one column? The only catch or
gt; difficulty is it has to be in the order of the rows vertically. See
gt; below.
gt; 12 15 45 20 12
gt; 13 15 45 20 15
gt; 14 15 45 20 45
gt; 16 15 45 20 20
gt; 17 15 45 20 13
gt; 18 15 45 20 15
gt; 19 15 45 20 45
gt; 20 15 45 20 20
gt; 21 15 45 20 14
gt; 15
gt; 45
gt; 20
gt; 16
gt; 15
gt; 45
gt; 20
gt; 17
gt; 15
gt; 45
gt; 20
gt; 18
gt; 15
gt; 45
gt; 20
gt;
gt; I did create a macro which is repetitive and was wondering if anyone
gt; can help make is simpler this way it transpose more than 100 rows of
gt; data into one column.
gt; Range(quot;A11quot;).Select
gt; Selection.Copy
gt; Range(quot;F1quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll,
gt; Operation:=xlNone,SkipBlanks:=False ,
gt; Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;A22quot;).Select
gt; Selection.Copy
gt; Range(quot;F5quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
gt; SkipBlanks:=False _
gt; , Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;A33quot;).Select
gt; Selection.Copy
gt; Range(quot;F9quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
gt; SkipBlanks:=False _
gt; , Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;A44quot;).Select
gt; Selection.Copy
gt; Range(quot;F13quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
gt; SkipBlanks:=False _
gt; , Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;A55quot;).Select
gt; Selection.Copy
gt; Range(quot;F17quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
gt; SkipBlanks:=False _
gt; , Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;A66quot;).Select
gt; Selection.Copy
gt; Range(quot;F21quot;).Select
gt; Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
gt; SkipBlanks:=False _
gt; , Transpose:=True
gt; Application.CutCopyMode = False
gt; Range(quot;E1quot;).Select
gt; End Sub
gt;
gt;
gt; --
gt; walan
gt; ------------------------------------------------------------------------
gt; walan's Profile:
gt; www.excelforum.com/member.php...oamp;userid=13528
gt; View this thread: www.excelforum.com/showthread...hreadid=531240
gt;