Change orientation of data - is this a transform?

A

Angus Comber

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
 
B

Bob Umlas

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
 
Top