Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

Hi George!

Yes, you can definitely accomplish this using standard worksheet functions in Excel. Here's how you can do it:

1. First, select the range of cells that contains your vertical blocked list.
2. Next, copy the range by pressing Ctrl+C or right-clicking and selecting "Copy".
3. Now, select the cell where you want to start your horizontal list.
4. Right-click on the cell and select "Paste Special".
5. In the "Paste Special" dialog box, select the "Transpose" checkbox and click "OK".
6. Your vertical blocked list should now be converted to a horizontal list!

If you have any formatting issues, you can adjust the width of the columns or use the "Wrap Text" feature to make sure all the data fits in the cells.

One way:

Assume your data starts in cell A1:

=INDEX($A:$A,ROW(A1)*10-10+COLUMN(A1))

Copy across 10 columns then down until you you get returns of 0. (assuming
you have empty cells at the end of the data list)

Then you can do a CopyPaste SpecialValues to convert the formulas to
constants.

Biff

"G Lykos" wrote in message
...
Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row,
with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

It really depends on how many records you have. If not too many, you can
select the ten rows, press <Ctrl<C, select the first cell, right click,
select Paste Special, and tick the Transpose option and OK out. Row 1 - 10
will become row 1, Col A:J. You will then have to delete rows 2-10, and
repeat the whole excercise. Alternatively, you can achieve the exact same
result with a macro, which takes a while to code, but does the job a lot
quicker.
--
Hth

Kassie Kasselman


"G Lykos" wrote:

Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

Oops again. You have to select B1 before doing the Paste Special thing, not
A1! Once everything is deleted, you must then delete Col A.
--
Hth

Kassie Kasselman


"G Lykos" wrote:

Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

One way ..

Assuming source data in A1 down, with data in 10 lines per group viz:
A1:A10, A11:A20, A21:A30 etc

Put in B1:
=INDEX($A:$A,ROW(A1)*10-10+COLUMN(A1))
Copy B1 across by 10 cols to K1, then fill down until zeros appear,
signalling exhaustion of data. Cols B to K will return the required results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"G Lykos" wrote:
Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

If you need code to do the job, and if it is true that each record contains
10 rows, then click on <Alt<F11 while in your spreadsheet. Click on
Insert, Module.
In the right hand pane, paste the following code:

Sub Swing()
Do While ActiveCell < ""
ActiveCell.Offset.Range("A1:A10").Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(1, -1).Select
ActiveCell.Offset.Range("A1:A9").Select
Selection.EntireRow.Delete
Loop
End Sub

Return to your worksheet, select the first cell, (I presume it is A1), press
<Alt<F8, select Swing and OK out. I did not set screen updating to false,
so you will see a lot of action as it loops through the code, but not to
worry! Once the macro has run, make sure that you are happy with the result
before saving, or ideally, save the file with a different name, eg by
suffixing UPD to the filename -
List.xls would become ListUPD.xls.
--
Hth

Kassie Kasselman


"G Lykos" wrote:

Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

Copy everything and then Edit = Paste special (check "Transpose" checkbox)

Joerg Mochikum




"G Lykos" wrote in message
...
Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row,
with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George

Try entering the references for the first two lines:

A01 A02 A03 A04 A05 A06 A07 A08 A09
A11 A12 A13 A14 A15 A16 A17 A18 A19

(As a shortcut enter only the first cell and drag the lower right
corner right.)
Then select this 2 x 10 block and drag the fill handle, in the lower
right corner down, as far as is needed followed by Edit Replace "A"
with "=A"

G Lykos wrote:

Greetings! Have an Excel database that has ten lines per entry in one
column in a symmetric structure - first line is name, second line is
address, etc. Would like to rotate the data so each entry is one row, with
the lines in successive columns 1, 2, etc. Can this be accomplished using
standard worksheet functions? If not, is there some code available?

Thanks,
George