change row to column
 

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?
--
Thank You

change row to column
 

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 EditCopy
Then, EditPaste SpecialValuesOK

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


--
Biff
Microsoft Excel MVP


"Humbled Learner" wrote in
message ...
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?
--
Thank You

change row to column
 

If your names are all in column A in pairs of 5 rows, this macro will
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 = False
On Error GoTo 20

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 = True

Range("A1").Select
Selection.Resize(Selection.Rows.Count + 4, _
Selection.Columns.Count).Select
Selection.Copy
Range("D65000").End(xlUp).Offset(1, 0).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").Select
Selection.Resize(Selection.Rows.Count + 4, _
Selection.Columns.Count).ClearContents
Application.CutCopyMode = False

10
Range("A1").End(xlDown).Select
Selection.Resize(Selection.Rows.Count + 4, _
Selection.Columns.Count).Select
Selection.Copy
Range("D65000").End(xlUp).Offset(1, 0).PasteSpecial
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A1").End(xlDown).Select
Selection.Resize(Selection.Rows.Count + 4, _
Selection.Columns.Count).ClearContents
Application.CutCopyMode = False
GoTo 10
Application.ScreenUpdating = True

20
Range("D1").Select
Application.ScreenUpdating = True
End Sub
======================


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47916