U
USJT
Name
Address
City
State
ZIp
email
and make it look like
Name Address City State Zip Email
Address
City
State
ZIp
and make it look like
Name Address City State Zip Email
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
N
Niek Otten
Select your data,
Edit>Copy
Select destination cell
Edit>Paste special, check Transpose
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Name
| Address
| City
| State
| ZIp
| email
| and make it look like
|
| Name Address City State Zip Email
Edit>Copy
Select destination cell
Edit>Paste special, check Transpose
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Name
| Address
| City
| State
| ZIp
| and make it look like
|
| Name Address City State Zip Email
S
SteveG
USJT,
Copy your list of values then in the first cell where you want to have
the data right mouse click, Paste Special and select Transpose from the
options and click OK.
HTH
Steve
Copy your list of values then in the first cell where you want to have
the data right mouse click, Paste Special and select Transpose from the
options and click OK.
HTH
Steve
G
Gord Dibben
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
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
F
flickflick
If you wanted to transpose a column with formulaes, I would like t
recommend a free add-in/on that I found just last week a
http://www.asap-utilities.com - its really very good and ver
intuitive
recommend a free add-in/on that I found just last week a
http://www.asap-utilities.com - its really very good and ver
intuitive
T
Tricia Young
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
person. Totals about 3000 rows. Please give me more info on any method that
is better than selecting each set and transposing.
Tricia
L
Lori
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.
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.
L
Lori
Tricia, It was assumed that the data was at regular intervals and
contained all fields. You'll need to add a field column that tells you
which line is which: Name, Address, Phone, etc. and then maybe use a
lookup formula. Without any field info there's no way to tell what goes
with what.
contained all fields. You'll need to add a field column that tells you
which line is which: Name, Address, Phone, etc. and then maybe use a
lookup formula. Without any field info there's no way to tell what goes
with what.
G
Gord Dibben
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
Gord Dibben MS Excel MVP
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
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
Gord Dibben MS Excel MVP
T
Tricia Young
Gord, where do i place this code. Please provide me step by step directions.
I am only familiar with VBA w/ Access.
Thanks,
Tricia
I am only familiar with VBA w/ Access.
Thanks,
Tricia
G
Gord Dibben
Tricia
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Select the column....easiest if you copy just the column of addresses to Column
A of a new sheet.
Run the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord
Gord Dibben MS Excel MVP
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Select the column....easiest if you copy just the column of addresses to Column
A of a new sheet.
Run the macro by going to Tool>Macro>Macros.
You can also assign this macro to a button or a shortcut key combo.
Gord
Gord, where do i place this code. Please provide me step by step directions.
I am only familiar with VBA w/ Access.
Thanks,
Tricia
Gord Dibben MS Excel MVP