swap rows and columns

I have a sheet in this form:
name1
address1
city1
name2
address2
city2

etc. for ~ 5000 names
I would like to move all the names to one column, addresses to the next
column and cities to the 3rd column so ai can sort the data on the names.
How could I do this in an easy manner?

Thank You in advance.
Rudy

Assuming that your values start in cell A2, use these formulas in cell:

B2 = A2
C2 = A3
D2 = A4

Then copy B2:D4, and paste to B5:D????, where ???? is the row with the last
entry.

Then copy columns B:D, and paste special values, then delete column A and
sort A:C based on column A, which will make all the blanks go away.

HTH,
Bernie
MS Excel MVP



"rudyh" <rudyh@discussions.microsoft.com> wrote in message
news:82B4F41C-5EC0-4034-96E1-D565DA6E2A22@microsoft.com...
>I have a sheet in this form:
> name1
> address1
> city1
> name2
> address2
> city2
>
> etc. for ~ 5000 names
> I would like to move all the names to one column, addresses to the next
> column and cities to the 3rd column so ai can sort the data on the names.
> How could I do this in an easy manner?
>
> Thank You in advance.
> Rudy
>

Rudy

If data is in sets of three as you describe and assuming in column A....

Enter this formula in B1 and copy across to D1 and down B,C and D until you
gets zeros.

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

Copy this range and Paste Special(in place)>Value>OK>Esc.

Delete column A.


Gord Dibben Excel MVP

On Wed, 14 Dec 2005 13:14:59 -0800, "rudyh" <rudyh@discussions.microsoft.com>
wrote:

>I have a sheet in this form:
>name1
>address1
>city1
>name2
>address2
>city2
>
>etc. for ~ 5000 names
>I would like to move all the names to one column, addresses to the next
>column and cities to the 3rd column so ai can sort the data on the names.
>How could I do this in an easy manner?
>
>Thank You in advance.
>Rudy

Actually quite easy using the Copy/Paste Special options

What your trying to do is move the data from rows to columns.
Simply select all of the rows of data you wish to move/copy. In your
examply you have 6 rows (select all rows).

From the Task Bar select Edit - Copy

Open a new sheet and position your cursor in cell A1 of the sheet

From the Task Bar select Edit - Paste Special and select the Transpose Box
appearing in the bottom 3rd of the options available

Select OK

Your Done

What was in Cells A1:A6 now appears as column headers in Columns A1:F1

I use this option frequently.

"rudyh" wrote:

> I have a sheet in this form:
> name1
> address1
> city1
> name2
> address2
> city2
>
> etc. for ~ 5000 names
> I would like to move all the names to one column, addresses to the next
> column and cities to the 3rd column so ai can sort the data on the names.
> How could I do this in an easy manner?
>
> Thank You in advance.
> Rudy
>