transposing vertical data to horizontal with varying amount of data

[Ghosty]

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

 

[Ken Johnson]

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

 

[Ghosty]

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.

 

[Ken Johnson]

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

 

[Ghosty]

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

 

[Ken Johnson]

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