Change orientation of data - is this a transform?

Hello

I have columns like this:

Name, Company, Address, Telephone

with more than a thousand rows.

I need to present the data like this:


Name
Company
Address
Telephone

Name
Company
Address
Telephone

etc

An example might be:

Liz Hurley
Models Inc
1 The Avenue, London, SW1 6TT
020 8888 7777

Tom Jones
Singers Ltd
2 The Avenue, London, SW1 6TT
020 8888 6666

etc
etc

How do I do that?

ie is there some automated way?

Angus

Assuming your data is in A1:D1000:
1 - in an empty column, say F, enter this in F1:F4
xa1
xb1
xc1
xd1
2 - select F1:F5 (includes a blank cell)
3 - Drag the fill handle down to F5000 (this can be done pretty quickly by
resizing the excel window (entire application) to be a small height, then
drag the fill handle down near the bottom of the screen (outside the
application -- goes VERY quickly)
4 - you now have
xa2
xb2
xc2
xd2 etc in cells F6:f9
and xa3
xb3
xc3
xd3 in F11:F14, etc, ending in xd1000

Select column F, ise Edit/Replace, and replace x with = and you're pretty
much done!

Bob Umlas
Excel MVP
see my book "This isn't Excel, it's magic" at
http://www.iil.com/iil/excelmagic


"Angus Comber" <[email protected]> wrote in message
news:%[email protected]...
> Hello
>
> I have columns like this:
>
> Name, Company, Address, Telephone
>
> with more than a thousand rows.
>
> I need to present the data like this:
>
>
> Name
> Company
> Address
> Telephone
>
> Name
> Company
> Address
> Telephone
>
> etc
>
> An example might be:
>
> Liz Hurley
> Models Inc
> 1 The Avenue, London, SW1 6TT
> 020 8888 7777
>
> Tom Jones
> Singers Ltd
> 2 The Avenue, London, SW1 6TT
> 020 8888 6666
>
> etc
> etc
>
> How do I do that?
>
> ie is there some automated way?
>
> Angus
>
>

Thanks. Yes that is magic - I will look at your book for sure.

Angus


"Bob Umlas" <[email protected]> wrote in message
news:%[email protected]...
> Assuming your data is in A1:D1000:
> 1 - in an empty column, say F, enter this in F1:F4
> xa1
> xb1
> xc1
> xd1
> 2 - select F1:F5 (includes a blank cell)
> 3 - Drag the fill handle down to F5000 (this can be done pretty quickly by
> resizing the excel window (entire application) to be a small height, then
> drag the fill handle down near the bottom of the screen (outside the
> application -- goes VERY quickly)
> 4 - you now have
> xa2
> xb2
> xc2
> xd2 etc in cells F6:f9
> and xa3
> xb3
> xc3
> xd3 in F11:F14, etc, ending in xd1000
>
> Select column F, ise Edit/Replace, and replace x with = and you're pretty
> much done!
>
> Bob Umlas
> Excel MVP
> see my book "This isn't Excel, it's magic" at
> http://www.iil.com/iil/excelmagic
>
>
> "Angus Comber" <[email protected]> wrote in message
> news:%[email protected]...
>> Hello
>>
>> I have columns like this:
>>
>> Name, Company, Address, Telephone
>>
>> with more than a thousand rows.
>>
>> I need to present the data like this:
>>
>>
>> Name
>> Company
>> Address
>> Telephone
>>
>> Name
>> Company
>> Address
>> Telephone
>>
>> etc
>>
>> An example might be:
>>
>> Liz Hurley
>> Models Inc
>> 1 The Avenue, London, SW1 6TT
>> 020 8888 7777
>>
>> Tom Jones
>> Singers Ltd
>> 2 The Avenue, London, SW1 6TT
>> 020 8888 6666
>>
>> etc
>> etc
>>
>> How do I do that?
>>
>> ie is there some automated way?
>>
>> Angus
>>
>>
>
>