Tricia
Tested on your example data. Note that Ken Johnson is original author.
Public Sub TransposePersonalData()
'ken johnson July 29, 2006
'transpose uneven sets of data with a blank row between
Application.ScreenUpdating = False
Dim rngData As Range
Dim iLastRow As Long
Dim i As Long
Dim iDataColumn As Integer
iDataColumn = Selection.Column
iLastRow = Cells(Application.Rows.Count, iDataColumn).End(xlUp).Row
i = Selection.Row - 1
Do While ActiveCell.Row < iLastRow
i = i + 1
Set rngData = Range(ActiveCell, ActiveCell.End(xlDown))
rngData.Copy
Cells(i, iDataColumn + 1).PasteSpecial Transpose:=True
rngData.Cells(rngData.Cells.Count + 2, 1).Activate
Loop
End Sub
Gord
On Fri, 1 Dec 2006 05:21:00 -0800, Tricia Young wrote:
Lori, thanks for trying to help me. I did what you suggested but notice the
undesirable outcome...
Column A
B. Kenneth Sanden
12 East 14th St., Ste.5B
New York;NY;10003
(212) 741-2700
Baldassano Architect Group
200 Parkway Dr. South
Hauppauge, NY;11788
(516) 864-2500
550 North Maple Ave.
Ridgewood, NJ 07450
(201) 445-2322
(201) 445-3053
Bčatty Harvey & AsO:lates
12 West 32nd Street
New York;NY;10001
(212) 563-0565
(212) 563-0617
Belmont Freeman
110 West 40th St.
New York;NY;
Web Page: http://(212) 382-3311
Ben net - Lowrey
180 Varick St., 12th Fl.
New York;NY;10014
(212) 209-1199
Berger Rait Design Assos
345 Seventh Avenue
New York;NY;10001
(212) 742-7000
41 Eas -
New York;NY;10003
(212) 777-7800
(212) 475-7424
Beyer Blinder Belie
Kramer, Caries
41 East 11 Street
New York;NY;10003
(212) 777-7800
(212) 475-7424
Barry Poskanzer, ALL Poskanzer, Barry
Beyer Blinder Belle
MeKean, Douglas McR
41 East 11 Street
New York;NY;10003
(212) 777-7800
(212) 475-7424
Beyer Blinder Belle
Scaglione, Bob
41 East 11 Street
New York;NY;10003
(212) 777-7800
(212) 475-7424
Beyer Blinder Belle
Virgil, David
41 East 11 Street
New York;NY;10003
(212) 777-7800
(212) 475-7424
BJL3 Engineers & Architects
Benjamin B Tue, P, E
;Vice President
393 Jericho Tpk.
Mineola, NY;11501
(516) 741-2222
Blaufeux & Blaufeux Architects
104 Hoyt Street
Brooklyn;NY;11217
(718) 875-2113
42 West 39 Street
New York;NY;10018
(212) 391-4919
Outcome was...
B. Kenneth Sanden 12 East 14th St., Ste.5B New York;NY;10003 (212)
741-2700
Baldassano Architect Group 200 Parkway Dr. South Hauppauge, NY;11788
(516) 864-2500
New York;NY;10003 (212) 741-2700 0 Baldassano Architect Group
Hauppauge, NY;11788 (516) 864-2500 0 550 North Maple Ave.
0 Baldassano Architect Group 200 Parkway Dr. South Hauppauge, NY;11788
0 550 North Maple Ave. Ridgewood, NJ 07450 (201) 445-2322
200 Parkway Dr. South Hauppauge, NY;11788 (516) 864-2500 0
Ridgewood, NJ 07450 (201) 445-2322 (201) 445-3053 0
(516) 864-2500 0 550 North Maple Ave. Ridgewood, NJ 07450
(201) 445-3053 0 Bčatty Harvey & AsO:lates 12 West 32nd Street
550 North Maple Ave. Ridgewood, NJ 07450 (201) 445-2322 (201) 445-3053
Bčatty Harvey & AsO:lates 12 West 32nd Street New York;NY;10001 (212)
563-0565
(201) 445-2322 (201) 445-3053 0 Bčatty Harvey & AsO:lates
New York;NY;10001 (212) 563-0565 (212) 563-0617 0
0 Bčatty Harvey & AsO:lates 12 West 32nd Street New York;NY;10001
Bohler Engineering,
40 Easst Merrick Rd., #110
Valley Stream, NY;11580
(516) 872-2000
"Lori" wrote:
One method is to enter the references of the cells for the first two
rows then fill down to the end of the data and do a replace to get the
values.
So if you want to transpose data every six rows, try entering somewhere
on the same sheet:
a1 a2 a3 a4 a5 a6
a7 a8 a9 a10 a11 a12
(To do this you only have to enter a1 then drag the right corner across
to a12 and put the rows under each other.)
Then select this 2x6 block and drag down the right corner as far as
necessary.
To finish choose Edit Replace Find:a Replace with:=a.
Tricia Young wrote:
Gord; I need help! I have a column that has the exact setup as the previous
person. Totals about 3000 rows. Please give me more info on any method that
is better than selecting each set and transposing.
Tricia
"Gord Dibben" wrote:
The Transpose function others have posted is fine for your sample but I'll bet
you have a whole column full of these names and addresses.
If you have, there are better methods than selecting each set and transposing.
Post back if more help needed.
Gord Dibben MS Excel MVP
On Fri, 18 Aug 2006 13:27:02 -0700, USJT wrote:
Name
Address
City
State
ZIp
email
and make it look like
Name Address City State Zip Email
Gord Dibben MS Excel MVP