List/Paste/Transpose?

[cchrisj]

I have a very long list (column) of records. It takes up only Column A. I need to transpose each record so each is in row (across 5 columns). Each record is the same number of cells (5) and the value of the last entry of each record is the same ("map"). Is there a formula/macro that will allow me to easily transpose each record from the column to a row?

THANKS for the consideration!

 

[Kelethaar]

if the data is cyclic you could make a macro kind of like this: keep in mind this is very simple, and will only work if the data you want to transpose follows the exact cycle throughout your spreadsheet. Make a command button somewhere near 0the top of your spread sheet, add this code (you can change the range("A1") to which column you have the data in).

Remember to test code on a copy of your spreadsheet before employing it in your valuable data.

Private Sub CommandButton1_Click()
Range("A1").Select
Do While ActiveCell <> ""
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(1, 0)
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(2, 0)
    ActiveCell.Offset(0, 3) = ActiveCell.Offset(3, 0)
    ActiveCell.Offset(0, 4) = ActiveCell.Offset(4, 0)
    
    ActiveCell.Offset(1, 0).ClearContents
    ActiveCell.Offset(2, 0).ClearContents
    ActiveCell.Offset(3, 0).ClearContents
    ActiveCell.Offset(4, 0).ClearContents
    
    ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell <> ""
        Selection.EntireRow.Delete
    Loop
Loop
End Sub

 

[Kelethaar]

scratch that... that would have put you in an infinite loop, Sorry.

this won't throw you in an infinite loop

Private Sub CommandButton1_Click()
Range("A1").Select
Do While ActiveCell <> ""
    ActiveCell.Offset(0, 1) = ActiveCell.Offset(1, 0)
    ActiveCell.Offset(0, 2) = ActiveCell.Offset(2, 0)
    ActiveCell.Offset(0, 3) = ActiveCell.Offset(3, 0)
    ActiveCell.Offset(0, 4) = ActiveCell.Offset(4, 0)
    
    ActiveCell.Offset(1, 0).ClearContents
    ActiveCell.Offset(2, 0).ClearContents
    ActiveCell.Offset(3, 0).ClearContents
    ActiveCell.Offset(4, 0).ClearContents
    
    ActiveCell.Offset(1, 0).Select
    
    Selection.EntireRow.Delete
    Selection.EntireRow.Delete
    Selection.EntireRow.Delete
    Selection.EntireRow.Delete
    
Loop

End Sub

sorry for the trouble, and I hope this helps,
Kelethaar

 

[Peter_SSs]

Formula approach:

1. Set up D1:H1 as shown below manually.

2. Enter C2:C3 manually then select and copy down as far as you might need.

3. Stand-alone formula in C1 (adjust range if > 1,000 rows)

4. Enter formula in D2 (adjust range if > 1,000 rows)

5. Copy D2 formula across and down

Excel Workbook
	A	B	C	D	E	F	G	H
1	List		25	1	2	3	4	5
2	Item 1		0	Item 1	Item 2	Item 3	Item 4	Item 5
3	Item 2		5	Item 6	Item 7	Item 8	Item 9	Item 10
4	Item 3		10	Item 11	Item 12	Item 13	Item 14	Item 15
5	Item 4		15	Item 16	Item 17	Item 18	Item 19	Item 20
6	Item 5		20	Item 21	Item 22	Item 23	Item 24	Item 25
7	Item 6		25
8	Item 7		30
9	Item 8
10	Item 9
11	Item 10
12	Item 11
13	Item 12
14	Item 13
15	Item 14
16	Item 15
17	Item 16
18	Item 17
19	Item 18
20	Item 19
21	Item 20
22	Item 21
23	Item 22
24	Item 23
25	Item 24
26	Item 25
27
Transpose

Macro approach:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Transpose_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> BlockSize <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 5<br>    <SPAN style="color:#00007F">Const</SPAN> DestCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "D"<br>    <SPAN style="color:#00007F">Const</SPAN> DestFirstRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2<br>    <br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    nr = DestFirstRow<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> LR <SPAN style="color:#00007F">Step</SPAN> BlockSize<br>        Cells(nr, DestCol).Resize(, BlockSize).Value = _<br>            Application.Transpose(Cells(r, "A").Resize(BlockSize).Value)<br>        nr = nr + 1<br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>