From vertical address labels to horizontal list

[Skarlett_One]

I'm using Excel 2003, and I'm trying to go from address labels to a single line per household. I have nearly 10,000 lines to go through. Is there any way of doing it faster than copy/paste/delete ad nauseum. The original one looks like this

|Jane Doe
|123 Any Street

and I need it to be:

Jane Doe |123 Any Street| Postal Code| City

I don't know how to word it to search for this request. I've been searching for 2 days, and I can't find what I'm looking for.

Help?

 

[Skarlett_One]

Thank you. I tried this and it came back with Runtime error '9': Subscript out of range. I'm not sure what I did wrong?

 

[Rick Rothstein]

Thank you. I tried this and it came back with Runtime error '9': Subscript out of range.

What line of code was highlighted when you got that error message?

 

[Skarlett_One]

A cell in column E. Should it have been in column A? I'm sorry, I've never used a macros like this before, so am totally lost.

 

[Skarlett_One]

Results(N, 1) = Cells(R, C)

 

[Skarlett_One]

Ok....this is what it looks like before I've done anything to it:

[TABLE="width: 718"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Scott Allen[/TD]
[TD]Aisha Rizwan & Rizwan Shahid[/TD]
[TD]Peter & Sylvia De Guzman[/TD]
[/TR]
[TR]
[TD]26 Panamount Street NW[/TD]
[TD]30 Panamount Street NW[/TD]
[TD]34 Panamount Street NW[/TD]
[/TR]
[TR]
[TD]Job: f06-018-0023 SR: 00008833[/TD]
[TD]Job: f06-018-0024 SR: 00009174[/TD]
[TD]Job: f06-018-0025 SR: 00014848[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nizar A. Walji & Hamid Shivji[/TD]
[TD]Luanne Tran[/TD]
[TD]Petty By Wu & Ken Lo[/TD]
[/TR]
[TR]
[TD]46 Panamount Street NW[/TD]
[TD]111 Panatella Close NW[/TD]
[TD]106 Panamount Rise NW (SP)[/TD]
[/TR]
[TR]
[TD]Job: f06-018-0028 SR: 00015045[/TD]
[TD]Job: f06-036-0054 SR: 00007994[/TD]
[TD]Job: f06-039-0048 SR: 00007521[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Samuel & Julie Law[/TD]
[TD]Yongsheng Wang & Yonghong Sun[/TD]
[TD]Edward Chan & Jaylene Klaudt[/TD]
[/TR]
[TR]
[TD]110 Panamount Rise NW[/TD]
[TD]114 Panamount Rise NW (SP)[/TD]
[TD]194 Panamount Rise NW[/TD]
[/TR]
[TR]
[TD]Job: f06-039-0049 SR: 00007299[/TD]
[TD]Job: f06-039-0051 SR: 00007635[/TD]
[TD]Job: f06-039-0072 SR: 00007300[/TD]
[/TR]
</tbody>[/TABLE]

There's 2 spaces between each entry. Sometimes three. I need to somehow remove the "job" line and get the rest to be horizontal. I tried running the script again and it still came back with the same error. The highlighted row is the same. Results(N, 1) = Cells(R, C)

 

[Fluff]

Try this mod to Rick's code

Sub FixNameAddressLayout()
'Rick Rothstein
  Dim R As Long, C As Long, N As Long, LastCol As Long, LastRow As Long, Groups As Long, Results As Variant
[COLOR=#ff0000]  With Columns("A")
      .Replace "Job:*", "#N/A", xlPart, , False, , False, False
      .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With[/COLOR]
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Groups = Columns("A").SpecialCells(xlConstants).Areas.Count
  ReDim Results(1 To Groups * LastCol, 1 To 2)
  For R = 1 To LastRow [COLOR=#ff0000]Step [/COLOR][COLOR=#ff0000]4[/COLOR]
    For C = 1 To Cells(R, Columns.Count).End(xlToLeft).Column
      N = N + 1
      Results(N, 1) = Cells(R, C)
      Results(N, 2) = Cells(R + 1, C)
    Next
  Next
  Range("F1").Resize(UBound(Results), 2) = Results
End Sub

 

[Skarlett_One]

I tried that, and it came back with the same error. I am using Excel 2003, if that makes a difference?

 

[Fluff]

It works for me in both xl2003 & 2013, so the version shouldn't be a problem.
Is your data laid out like this

Excel 2013 32 bit
	A	B	C
1	Scott Allen	Aisha Rizwan & Rizwan Shahid	Peter & Sylvia De Guzman
2	26 Panamount Street NW	30 Panamount Street NW	34 Panamount Street NW
3	Job: f06-018-0023 SR: 00008833	Job: f06-018-0024 SR: 00009174	Job: f06-018-0025 SR: 00014848
4
5
6	Nizar A. Walji & Hamid Shivji	Luanne Tran	Petty By Wu & Ken Lo
7	46 Panamount Street NW	111 Panatella Close NW	106 Panamount Rise NW (SP)
8	Job: f06-018-0028 SR: 00015045	Job: f06-036-0054 SR: 00007994	Job: f06-039-0048 SR: 00007521
9
10
11	Samuel & Julie Law	Yongsheng Wang & Yonghong Sun	Edward Chan & Jaylene Klaudt
12	110 Panamount Rise NW	114 Panamount Rise NW (SP)	194 Panamount Rise NW
13	Job: f06-039-0049 SR: 00007299	Job: f06-039-0051 SR: 00007635	Job: f06-039-0072 SR: 00007300
Sheet2

 

[Skarlett_One]

Yes, and sometimes there's 3 spaces in between.

I was able to get it to work by putting all the information into one column and then getting it to separate out from there. Now I have to try to sort it according to address. I tried sorting by ascending in column B, but it doesn't keep all the same subdivision street names together. Is there a macros for that as well?

 

[Fluff]

Now I have to try to sort it according to address. I tried sorting by ascending in column B, but it doesn't keep all the same subdivision street names together. Is there a macros for that as well?

Almost certainly, but that's beyond me.