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]

I forgot to mention, there are 3 columns of addresses that are stacked.

 

[Fluff]

Hi & welcome to the board.
Could you supply us with a slightly larger sample of how your data looks? There are tools to help with this here https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Skarlett_One]

Hi & welcome to the board.
Could you supply us with a slightly larger sample of how your data looks?

This:

[FONT=&quot]|Jane Doe |Frank Fellow |Charlie Dude
|123 Any Street |234 Your Street |567 That Street

|Mary Moe |Tom Thumb |Richard Row
|222 Town Road |303 City Place |555 Village Row

and I need it to be:

Jane Doe |123 Any Street| Postal Code| City[/FONT]
[FONT=&quot]Frank Fellow |234 Your Street| Postal Code | City[/FONT]
[FONT=&quot]Charlie Dude |567 That Street| Postal Code | City[/FONT]
Etc...

 

[Skarlett_One]

And thanks for the welcome.

 

[Fluff]

Ok, a few more questions
Where do Postal Code & City come from?
Does the | represent a new column, or is that part of your data?
Are there blank rows between each set of people?

 

[Skarlett_One]

Ok, a few more questions
Where do Postal Code & City come from?
Does the | represent a new column, or is that part of your data?
Are there blank rows between each set of people?

Postal Code/City is something I have to add in after. Yes, the | is a new column. There are blank rows in between each set, and each set is on 2 rows. I have brought it down from over 54,000 lines to just over 7,000 as of now, but most of that was copy/paste. I'd rather not have to spend another couple days working on this. It's giving me RSI! *laugh*

 

[Rick Rothstein]

Postal Code/City is something I have to add in after. Yes, the | is a new column. There are blank rows in between each set, and each set is on 2 rows. I have brought it down from over 54,000 lines to just over 7,000 as of now, but most of that was copy/paste. I'd rather not have to spend another couple days working on this. It's giving me RSI! *laugh*

You should have come to us right away... we might have been able to work directly with your original data (depending on its format) to produce your desired result in a single (coded) step. Out of curiosity, does your original data contain the postal codes and city names?

 

[Skarlett_One]

No, it doesn't. It's all from either the city I'm in or one of the two bedroom communities around, so shouldn't be hard. Though trying to get the addresses on a group of streets to go in order from smallest to largest is also proving a problem.

I've been trying to figure it out for a couple of days. I was using Excel online, but it wouldn't do what I needed, so I tried Google sheets, which was about the same. I even used Open Office Calc... but now I'm in Excel 2003, hoping to be able to get this figured out. Like I said in my original post, I wasn't sure how to word it, so couldn't find how to do it anywhere. :/ Quite frustrating.

 

[Rick Rothstein]

Give this macro a try (it outputs the values you want in Columns F:G... you fill in Column H:I later)...

[table="width: 500"]
[tr]
	[td]Sub FixNameAddressLayout()
  Dim R As Long, C As Long, N As Long, LastCol As Long, LastRow As Long, Groups As Long, Results As Variant
  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 Step 3
    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[/td]
[/tr]
[/table]