change row to column

[Humbled Learner]

Hi,

I imported an address book. Column A contains all the information, 430
lines consisting of 1 name, 2 address, 3 city, 4 State, 5 Zip, 6 name, 7
address, 8 city, 9 State, 10 Zip 11 name and so forth.

Is there an easy way to move the row to columns each in their own block?

 

[T. Valko]

Is there an easy way to move the row to columns

each in their own block?

Easy is a tricky term. It means different things to different people!

Let's assume your data is in the range A2:A21.

Enter this formula in C2:

=INDEX($A$2:$A$21,(ROWS($1:1)-1)*5+COLUMNS($A:B)-1)

Copy across to G2 then down to row 5.

After you have the data transposed inspect it and make sure it's correct.

Select the entire range of formulas
Goto the menu Edit>Copy
Then, Edit>Paste Special>Values>OK

Now you can delete the original data in column A (if you want to).

 

[JBeaucaire]

If your names are all in column A in pairs of 5 rows, this macro wil
re-sort them into Columns D through H, complete with labels

Try it on a copy of your data, make sure Columns D through H are clear

=============Macro========
Sub SortAddressList(
Application.ScreenUpdating = Fals
On Error GoTo 2

Range("D1").FormulaR1C1 = "Name
Range("E1").FormulaR1C1 = "Address
Range("F1").FormulaR1C1 = "City
Range("G1").FormulaR1C1 = "State
Range("H1").FormulaR1C1 = "Zip
Range("D1:H1").Font.Bold = Tru

Range("A1").Selec
Selection.Resize(Selection.Rows.Count + 4,
Selection.Columns.Count).Selec
Selection.Cop
Range("D65000").End(xlUp).Offset(1, 0).PasteSpecia
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Tru
Range("A1").Selec
Selection.Resize(Selection.Rows.Count + 4,
Selection.Columns.Count).ClearContent
Application.CutCopyMode = Fals

10
Range("A1").End(xlDown).Selec
Selection.Resize(Selection.Rows.Count + 4,
Selection.Columns.Count).Selec
Selection.Cop
Range("D65000").End(xlUp).Offset(1, 0).PasteSpecia
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Tru
Range("A1").End(xlDown).Selec
Selection.Resize(Selection.Rows.Count + 4,
Selection.Columns.Count).ClearContent
Application.CutCopyMode = Fals
GoTo 1
Application.ScreenUpdating = Tru

2
Range("D1").Selec
Application.ScreenUpdating = Tru
End Su
=====================