There must be a forumla for this :/

[ExcelBuddy]

Hi,
All my data is in Column A as shown below. Each line is a different row. Each addresses may be a different number of rows. How can I transpose several thousand of these addresses into columns, knowing that each addresses a varying number of rows. I don't mind modifying the addresses after the fact. Ultimately, I want row to have the first address, the second row to have the second address, etc. In the end, I want to be able to filter the info. As of right now, all I know how to do is highlight the info > copy > past special > transpose. Thanks in advance!

Current:
<TABLE style="WIDTH: 397pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=530><COLGROUP><COL style="WIDTH: 397pt; mso-width-source: userset; mso-width-alt: 18830" width=530><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 397pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=530>Alex Dimplestein (row 1) </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Heli Inc. (row 2, etc.)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>123 Water Drive </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Marlboro, MA 01752
Suite 125


</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Phone: (123) 456-654</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>E-mail: alex.dimp@heli.com



</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 397pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=530><COLGROUP><COL style="WIDTH: 397pt; mso-width-source: userset; mso-width-alt: 18830" width=530><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 397pt; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19 width=530>John Dimplestein (row 1) </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Heli Inc. (row 2, etc.)</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>123 Water Drive </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Marlboro, MA 01752</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>Phone: (123) 456-654</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=19>E-mail: john.dimp@heli.com





</TD></TR></TBODY></TABLE>

 

[Ruddles]

I only answered this question yesterday!

http://www.mrexcel.com/forum/showthread.php?t=499352

 

[ExcelBuddy]

I am not very Excel savvy, so I don't know how to use macros or VB. Also, the number of rows isn't always the same...

 

[Ruddles]

Fair enough. Okay, if someone wrote a macro, how would it determine which was the first row of each block? Is it in a different font, for example?

Or maybe the last row... does the last row always start with "Email:"?

Can the process which created this data apply some sort of marker to the first row of each block, or a standard separator between blocks?

 

[ExcelBuddy]

The last line in every section ends with a row that begins with "E-mail:" That's the only thing in common...

 

[VoG]

If you can get rid of extraneous blank rows so that each address is a block, each separated by at least one blank row then try

Sub Addys()
Dim aArea As Range
For Each aArea In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, aArea.Rows.Count) = Application.Transpose(aArea)
Next aArea
End Sub

 

[ExcelBuddy]

Hi Peter,

Your solution worked! In order to get to my final solution, how can I do the following...

Basically, I got rid of all the blank rows in column A. Now I want to create one blank row after each record, so that I can run your code. Again, the one thing consistent is that each record ends with "E-mail"

After that, I can run your code.

Thanks!

 

[VoG]

This will add the blank rows then transform the data

Sub Addys()
Dim aArea As Range, LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value Like "E-mail:*" Then Rows(i + 1).Insert
Next i
For Each aArea In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, aArea.Rows.Count) = Application.Transpose(aArea)
Next aArea
End Sub

 

[ExcelBuddy]

Worked perfectly, thanks!