Transpose in Excel?

Microsoft Excel 2007
September 2, 2009 at 10:35:42
Specs: Windows XP SP2
I have a column of 300 entries in which names are followed by addresses and then by city/state in three diffferent rows. I would like to convert this to 300 rows with one column containing names, another addresses and the third city/state. Transpose converts the row to a column but doesn't let me line up names with names, etc.
How do I automate this process.

See More: Transpose in Excel?

Report •


#1
September 2, 2009 at 11:39:36
but doesn't let me line up names with names
Not sure I understand what your trying to do.

MIKE

http://www.skeptic.com/


Report •

#2
September 2, 2009 at 12:36:48
I have this.....

Fred Wuffin
7601  Chruchill Way
Baton Rouge, LA    70803

John Freese
4055  International Plaza
Fort Worth, TX    76109

Ron Jaworski
2200  Ross Avenue
Dallas, TX    75201

Gruber Hurst
1445  Ross Avenue
Duncan, Ok    73533

Fred Hall
8616  NW Plaza Drive
Dallas, TX    75225

I want this.....
Fred Wuffin 7601  Chruchill Way Baton Rouge, LA    70803
John Freese 4055  International Plaza Fort Worth, TX    76109
Ron Jaworski 2200  Ross Avenue Dallas, TX    75201
Gruber Hurst 1445  Ross Avenue Duncan, Ok    73533
Fred Hall 8616  NW Plaza Drive Dallas, TX    75225


Report •

#3
September 2, 2009 at 13:25:49
I'll assume you actually have a blank row between each block of data.

I'll also assume that each name, address and city/state is in it's own cell in Column A, starting in A1.

In B1, next to Fred Wuffin, enter this formula in a cell:

=INDIRECT("A" & ROW())

In C1 enter:

=INDIRECT("A" & ROW() +1)

In D1 enter:

=INDIRECT("A" & ROW() +2)

You should now have all of Fred's data next to each in B1:D1.

Now highlight those 3 cells as well as the same 3 cells in the next 3 rows. In others words B1:D4.

Grab the Autofill handle and drag down to the bottom of your data.

You should now have your transposed data, separated by 3 blank lines each.

Do an Edit...Copy...Edit ...Paste Special...Values to get rid of the formulae and turn the data back into text.

Sort as desired to collapse the data.


Report •

Related Solutions

#4
September 2, 2009 at 13:53:28
Thanks. That works well.

Report •


Ask Question