Converting data in columns to data in rows

[shesajen]

Is there a way to take data that is arranged in columns and convert it into rows?

The data is currently listed like this:
<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 96pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=128 colSpan=2 height=17>DOE, JAMES</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>48 GUS ECKERT Apt # 405</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>SAN ANTONIO TX 78240</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>JANE SMITH</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>1314 EDGE POINT Apt # </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>SAN ANTONIO TX 78230</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>DOE, JOHN</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>1161 VANCE JACKSON Apt # 111</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>SAN ANTONIO TX 78239</TD></TR></TBODY></TABLE>

but I need it to be like this for a mail merge:
DOE, JOHN 1161 VANCE JACKSON APT #111 SAN ANTONIO TX 78239

To note:
The street address on the second row of data is a concatenate formula piecing the street #, street name and apt. # from another worksheet in the book. Same for city and state on the third row.

 

[hiker95]

shesajen,

Welcome to the MrExcel forum.


We can not tell where your raw data is located, cells, rows, columns,
and, we can not tell whare the results should be, cells, rows, columns.


Sample raw data in worksheet Sheet1 beginning in cell A1:

Excel Workbook
	A
1	DOE, JAMES
2	48 GUS ECKERT Apt # 405
3	SAN ANTONIO TX 78240
4	JANE SMITH
5	1314 EDGE POINT Apt #
6	SAN ANTONIO TX 78230
7	DOE, JOHN
8	1161 VANCE JACKSON Apt # 111
9	SAN ANTONIO TX 78239
10
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A
1	DOE, JAMES 48 GUS ECKERT Apt # 405 SAN ANTONIO TX 78240
2	JANE SMITH 1314 EDGE POINT Apt # SAN ANTONIO TX 78230
3	DOE, JOHN 1161 VANCE JACKSON Apt # 111 SAN ANTONIO TX 78239
4
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ConvertData()
' hiker95, 05/16/2012
' http://www.mrexcel.com/forum/showthread.php?t=636027
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, nr As Long, h As String
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
lr = w1.Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lr Step 3
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  If nr = 2 And wR.Cells(1, 1) = "" Then nr = 1
  h = w1.Cells(r, 1).Value & " " & w1.Cells(r + 1, 1).Value & " " & w1.Cells(r + 2, 1).Value
  wR.Cells(nr, 1) = h
Next r
wR.Columns(1).AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ConvertData macro.

 

[hiker95]

shesajen,


If the above screenshot of Sheet1 is not correct, then:


What version of Excel are you using?

Can you post the raw data, and, post the results you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.