| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I take a column of data and put it in a row
Name
Address City State ZIp and make it look like Name Address City State Zip Email |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
Select your data,
EditCopy Select destination cell EditPaste special, check Transpose -- Kind regards, Niek Otten Microsoft MVP - Excel "USJT" wrote in message ... | Name | Address | City | State | ZIp | and make it look like | | Name Address City State Zip Email |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=573255 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 and make it look like Name Address City State Zip Email |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
If you wanted to transpose a column with formulaes, I would like to recommend a free add-in/on that I found just last week at http://www.asap-utilities.com - its really very good and very intuitive. -- flickflick ------------------------------------------------------------------------ flickflick's Profile: http://www.excelforum.com/member.php...o&userid=37709 View this thread: http://www.excelforum.com/showthread...hreadid=573255 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 and make it look like Name Address City State Zip Email |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 and make it look like Name Address City State Zip Email |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 and make it look like Name Address City State Zip Email |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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. 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 and make it look like Name Address City State Zip Email |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 and make it look like Name Address City State Zip Email Gord Dibben MS Excel MVP |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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" wrote: 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 and make it look like Name Address City State Zip Email Gord Dibben MS Excel MVP |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I take a column of data and put it in a row
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 InsertModule. 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 ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord On Sat, 2 Dec 2006 23:33:00 -0800, Tricia Young wrote: 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" wrote: 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 and make it look like Name Address City State Zip Email Gord Dibben MS Excel MVP Gord Dibben MS Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
