Arrange columns of names & addresses into rows

[JohnnyBravo]

I've got a list of names and addresses that I'm trying to organize for a mail merge. These list of names are in a CSV format, I've attached a file as an example. What I am trying to achieve is the final product which looks like the 2nd screenshot. As you can see from the screenshot, there is no consistency where the addresses are. Some are in one cell, some are on 2 cells... to make things worse, the list of names & addresses are in a 3 column format.

What is the best way to achieve this? Is VBA script the most appropriate solution or is there an Excel function that would do the job?

Note: I had to upload the excel file as an *.xlsx file - as the forum does not allow for *.csv file attachments.

[mdmackillop]

Here's a start. It depends upon the length of the zipcode in each address
[VBA]
Option Explicit
Sub address()
Dim Arr(), a
Dim i%, j%, Col%, Rw%, x%
Dim cel As Range
ReDim Arr(5000)
For Col = 1 To 3
For Each cel In Intersect(Columns(Col), ActiveSheet.UsedRange)
If cel <> "" Then
Arr(j) = cel.Value
j = j + 1
End If
Next

Next
ReDim Preserve Arr(j - 1)
Rw = 1
i = 6
For x = 0 To UBound(Arr)
Cells(Rw, i) = Arr(x)
Cells(Rw, i).Select
If Arr(x) Like "*#####" Then
Rw = Rw + 1
i = 6
Else
i = i + 1
End If
Next

End Sub

[/VBA]

[JohnnyBravo]

The zip codes are always going to be 5 characters long. mdmackillop, thank you for your code - I will try it out right now and report back.

EDIT: Ok, I just tried your code and there's a few things I should clarify.

#1. I need the out of the new format in a new *.xlsx file. Name does not matter.
#2. Take a look at the 1st person John R. Doe in my screenshot above. His street address is in just one cell only compared to Jane Nobody's address. She's got 2 cells because the apartment number is located in a separate cell. When I run your code, the results is represented in the screenshot attached.
#3. Ideally, I would like to have First name in column A. Last name in column B. Zip code in column E.

[mdmackillop]

Sounds easy, but what is the last name of Thomas Jones Jr. Try splitting off the zip code yourself. Not too hard!

[JohnnyBravo]

Sounds easy, but what is the last name of Thomas Jones Jr.

First name = Thomas
Last Name = Jones

Not too hard!

Yep - that one I can manage no problem.

[mdmackillop]

Quote: Originally Posted by: mdmackillop Sounds easy, but what is the last name of Thomas Jones Jr.

First name = Thomas
Last Name = Jones

I worked that out, but how does one state the logic?

[JohnnyBravo]

I worked that out, but how does one state the logic?

Not sure what you're asking for but at any rate, to make this easier - let's just assume that all the names in my file have no jr. or sr... or any kind of suffix to deal with. In fact ,I just looked over my original csv file and out of the hundreds of names on the list, there's only 1 person with a suffix.

[mdmackillop]

[VBA]
Option Explicit
Sub address()
Dim Arr(), a
Dim i%, j%, Col%, Rw%, x%
Dim cel As Range
Dim Nme As String, n As Long
ReDim Arr(5000)
For Col = 1 To 3
For Each cel In Intersect(Columns(Col), ActiveSheet.UsedRange)
If cel <> "" Then
Arr(j) = cel.Value
j = j + 1
End If
Next

Next
ReDim Preserve Arr(j - 1)
Rw = 1
i = 6
For x = 0 To UBound(Arr)
Cells(Rw, i) = Arr(x)
Cells(Rw, i).Select
If Arr(x) Like "*#####" Then
Rw = Rw + 1
i = 6
Else
If i = 6 Then
Nme = Cells(Rw, 6)
n = InStrRev(Nme, " ") - 1
Cells(Rw, 6) = Left(Nme, n)
Cells(Rw, 7) = Right(Nme, Len(Nme) - n - 1)

i = i + 2
Else: i = i + 1
End If
End If
Next

End Sub
[/VBA]

[JohnnyBravo]

Still not there. The City and State are not populating consistently in column J for everyone like it should. Some are in column I and some are in column J. Run your code on the sample file I attached and you'll see what I mean. Basically what I need is the screenshot that I attached in my original post.

In a new Excel spreadsheet:
Col A = should have all the First Names
Col B = should have all the Last Name
Col C = Address and Apartment number
Col D = City
Col E = Zip Code

[mdmackillop]

Please post your own attempts at fixing the problem. We are here to assist, not to provide free solutions. Please refer to our FAQ

[JohnnyBravo]

Please post your own attempts at fixing the problem. We are here to assist, not to provide free solutions. Please refer to our FAQ

I would if I could. I am a novice at VBA so I don't know how to modify your code. At any rate, I appreciate all the help you've provided already so thanks mate. I'll find the solution elsewhere.