Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
transpose a column into many rows | Excel Discussion (Misc queries) | |||
Transpose into a _working_ transposed array | Excel Discussion (Misc queries) | |||
repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) | |||
TRANSPOSE() | Excel Worksheet Functions |