Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default transposing vertical data to horizontal with varying amount of data

Hi there I've searched through the forum and found lots of solutions
for transposing vertical to horizontal data. However all the solutions
seem to be based on the fact that the number of rows of data is a set
amount.

For example I have the following data in one sheet:

Name
Address
Tel
Email
[emptyrow]
Name
Address
Tel
[emptyrow]
Name
Address
Tel
Email
[emptyrow]
Name
Address
[emptyrow]


And I wish for it to get transposed to the following:

Name Address Tel Email

Is there a way to automatically do this with a formula whilst using
that empty row as a designation to start a new row? And to just skip a
cell when a group of data does not have information, eg "no Tel
specified". With all the current formulas using OFFSET based on a
certain amount of rows this obviously does not work.

Thanks

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default transposing vertical data to horizontal with varying amount of data

Ghosty wrote:
Hi there I've searched through the forum and found lots of solutions
for transposing vertical to horizontal data. However all the solutions
seem to be based on the fact that the number of rows of data is a set
amount.

For example I have the following data in one sheet:

Name
Address
Tel
Email
[emptyrow]
Name
Address
Tel
[emptyrow]
Name
Address
Tel
Email
[emptyrow]
Name
Address
[emptyrow]


And I wish for it to get transposed to the following:

Name Address Tel Email

Is there a way to automatically do this with a formula whilst using
that empty row as a designation to start a new row? And to just skip a
cell when a group of data does not have information, eg "no Tel
specified". With all the current formulas using OFFSET based on a
certain amount of rows this obviously does not work.

Thanks


Hi Ghosty,

This VBA code worked for me...

Public Sub TransposePersonalData()
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

Try it out on a backup copy of your data first.

Before you actually run the macro you MUST make sure that the very
first name at the top of the list is selected. Proper function of the
code relies on this.

Also, the transposed data will appear in the four columns immediately
to the right of the original column of untransposed data.

To get the code into place...

1. Copy it

2. Press Alt + F11 or go Tools|Macro|"Visual Basic Editor" to get into
the Visual Basic Editor.

3. In the Visual Basic Editor go Insert|Module then paste the code into
the Code Module that appears

4. Press Alt + F11 or go File|"Close and Return to Microsoft Excel" to
get back to the usual Excel interface.

5.Save

6.If the workbook's Security level is set at High or Very High the
macro won't work and you will need to set the Security level to Medium.
To do that Go Tools|Macro|Security... and select Medium, then close and
reopen the workbook. Every time the workbook is opened you will need to
click on "Enable Macros" on the "Security Warning" dialog.

Ken Johnson

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default transposing vertical data to horizontal with varying amount of data

Hi Ken,

Thanks a bunch that worked out great!

For some strange reason it popped a few records even further to the
right of the newly transposed data any ideas why?

It's not big deal though since it only did that to 5 out of 1000
"records"

Thanks again.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default transposing vertical data to horizontal with varying amount of data


Ghosty wrote:
Hi Ken,

Thanks a bunch that worked out great!

For some strange reason it popped a few records even further to the
right of the newly transposed data any ideas why?


Hi Ghosty,

Is it possible that the blank cell separating each group of records was
missing for those strange results?

That's all I can think of. The code I composed definitely relied on
that single blank cell separating the different groups.

Ken Johnson

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default transposing vertical data to horizontal with varying amount of data


Ken Johnson wrote:
Ghosty wrote:
Hi Ken,

Thanks a bunch that worked out great!

For some strange reason it popped a few records even further to the
right of the newly transposed data any ideas why?


Hi Ghosty,

Is it possible that the blank cell separating each group of records was
missing for those strange results?

That's all I can think of. The code I composed definitely relied on
that single blank cell separating the different groups.

Ken Johnson


Thanks a bunch you're a true excel guru! I tried it again with another
large batch of data and I think it may have been a missing blank row



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,073
Default transposing vertical data to horizontal with varying amount of data


Hi Ghosty,

thanks for those kind words!

I thought that would be the case. It was the only way I could get it to
place records further to the right. Luckily it's a simple problem to
deal with and doesn't stuff the rest of them up.

Ken Johnson

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
How to make Horizontal data go Vertical tx12345 Excel Worksheet Functions 10 December 24th 05 04:40 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
Transposing Data Amy_SATX Excel Worksheet Functions 0 September 6th 05 05:28 PM
Flip the X and Y data on a chart so that the Y data is vertical. Haynesz Charts and Charting in Excel 1 December 3rd 04 02:14 PM
How can I insert a vertical column break between data to create a. Mark Wisdom Excel Worksheet Functions 1 November 23rd 04 05:10 AM


All times are GMT +1. The time now is 04:27 AM.

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"