Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lois Lane
 
Posts: n/a
Default Convert multiple columns to rows

Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!
  #2   Report Post  
Alan Beban
 
Posts: n/a
Default

Lois Lane wrote:
Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, and
Tools|Options|View|Zero values is unchecked

=TRANSPOSE(MakeArray(A1:E2,1)) array entered

Alan Beban
  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

Lois,

Try this macro

Sub Reformat()
Dim i As Long
Dim cLastRow As Long

Application.ScreenUpdating = False
With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
.Cells(i + 1, "A").Resize(4, 1).EntireRow.Insert
.Cells(i + 1, "A").Value = .Cells(i, "B").Value
.Cells(i + 2, "A").Value = .Cells(i, "C").Value
.Cells(i + 3, "A").Value = .Cells(i, "D").Value
.Cells(i, "B").Resize(1, 3).ClearContents
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lois Lane" <Lois wrote in message
...
Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is

in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!



  #4   Report Post  
Gary Rowe
 
Posts: n/a
Default

You can accomplish with a pivot table. Set up the pivot table with your data
and put the company, person, city, telephone fields in rows and set the field
settings for each to outline. Also set the company field settings to include
a blank line after each one. Then in a cell outside of the pivot table enter
a formula to concatenate the four cells containing the data on each line such
as =B5&C5&D5&E5 where one field will have info and the three others will be
blank. The result will be the information (company, person, city, telephone)
in each row. You can then copy and paste/special and select values. Then
you can delete the pivot table.
Gary

"Lois Lane" wrote:

Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!

  #5   Report Post  
Jason Morin
 
Posts: n/a
Default

Another way would be to select the 5th col. on your data
sheet (assuming it's named "mysht"), enter ="", and press
<ctrl<enter. Now on a new sheet in A1 put:

=OFFSET(mysht!$A$1,ROUNDUP(ROW()/5,0)-1,ROW()-(ROUNDUP(ROW
()/5,0)*5-5)-1)

and copy down as far as needed.

HTH
Jason
Atlanta, GA

-----Original Message-----
Excel's help file provides a specific formula to convert

multiple rows to
columns. However, I need help in converting multiple

columns to rows,
placing a blank row inbetween each set of records. Right

now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown

(000)000-0000
XYZ Company Jane Smith Metropolis

(000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to

convert rows to
columns, but it didn't work.

Thanks, in advance!
.



  #6   Report Post  
Lois Lane
 
Posts: n/a
Default

Thank you, Gary - I'll try it!

"Gary Rowe" wrote:

You can accomplish with a pivot table. Set up the pivot table with your data
and put the company, person, city, telephone fields in rows and set the field
settings for each to outline. Also set the company field settings to include
a blank line after each one. Then in a cell outside of the pivot table enter
a formula to concatenate the four cells containing the data on each line such
as =B5&C5&D5&E5 where one field will have info and the three others will be
blank. The result will be the information (company, person, city, telephone)
in each row. You can then copy and paste/special and select values. Then
you can delete the pivot table.
Gary

"Lois Lane" wrote:

Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!

  #7   Report Post  
Lois Lane
 
Posts: n/a
Default

Worked like a charm - thank you!

"Bob Phillips" wrote:

Lois,

Try this macro

Sub Reformat()
Dim i As Long
Dim cLastRow As Long

Application.ScreenUpdating = False
With ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
.Cells(i + 1, "A").Resize(4, 1).EntireRow.Insert
.Cells(i + 1, "A").Value = .Cells(i, "B").Value
.Cells(i + 2, "A").Value = .Cells(i, "C").Value
.Cells(i + 3, "A").Value = .Cells(i, "D").Value
.Cells(i, "B").Resize(1, 3).ClearContents
Next i
End With
Application.ScreenUpdating = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lois Lane" <Lois wrote in message
...
Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is

in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to convert rows to
columns, but it didn't work.

Thanks, in advance!




  #8   Report Post  
Lois Lane
 
Posts: n/a
Default

Thank you, Alan!

"Alan Beban" wrote:

Lois Lane wrote:
Excel's help file provides a specific formula to convert multiple rows to
columns. However, I need help in converting multiple columns to rows,
placing a blank row inbetween each set of records. Right now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown (000)000-0000
XYZ Company Jane Smith Metropolis (000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, and
Tools|Options|View|Zero values is unchecked

=TRANSPOSE(MakeArray(A1:E2,1)) array entered

Alan Beban

  #9   Report Post  
Lois Lane
 
Posts: n/a
Default

This was by far the easiest solution! Thank you very much, Jason!
Initially, it wouldn't work, but I realized your formula was on two lines
instead of one - once I pasted it all on one line, it was perfect!

Thanks again.

"Jason Morin" wrote:

Another way would be to select the 5th col. on your data
sheet (assuming it's named "mysht"), enter ="", and press
<ctrl<enter. Now on a new sheet in A1 put:

=OFFSET(mysht!$A$1,ROUNDUP(ROW()/5,0)-1,ROW()-(ROUNDUP(ROW
()/5,0)*5-5)-1)

and copy down as far as needed.

HTH
Jason
Atlanta, GA

-----Original Message-----
Excel's help file provides a specific formula to convert

multiple rows to
columns. However, I need help in converting multiple

columns to rows,
placing a blank row inbetween each set of records. Right

now, the data is in
this format, representing 4 columns:

ABC Company John Doe Anytown

(000)000-0000
XYZ Company Jane Smith Metropolis

(000)555-5555

I need the data to be formatted as such:

ABC Company
John Doe
Anytown
(000)000-0000

XYZ Company
Jane Smith
Metropolis
(000)555-5555

I tried modifying the formula provided by Microsoft to

convert rows to
columns, but it didn't work.

Thanks, in advance!
.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I convert columns to rows? Adam@Penda Excel Discussion (Misc queries) 3 January 11th 05 08:35 PM
how do I easily convert a single column of text (multiple rows si. philmah Excel Discussion (Misc queries) 2 December 16th 04 01:24 AM
interchange columns with rows Herman Excel Discussion (Misc queries) 2 December 8th 04 06:40 PM
how to convert multiple columns of data into one single column? Ah 3 Excel Worksheet Functions 1 November 12th 04 03:33 PM
Count rows based on multiple criteria Murph Excel Worksheet Functions 1 October 28th 04 07:13 AM


All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"