Moving One Column To Multiple Rows and Multiple Cells

[cartley]

I'm hoping someone can help with manipulating data from one column to multiple rows and cells in an easy fashion. We copy and paste hundreds of names for mailing lists into one column in the format you see below. We are trying to quickly convert the data so that if Name One is in cell a1, then address one would move to b1, city to c1, state to d1 and zip to e1. Then if name two is in cell a4, then address two would move to b4, city to d4 and zip to e4. I know about the transpose function but it is far too time consuming to do that over and over for the number of entries we need to address. Anything faster would be much appreciated. Thanks!

<colgroup><col style="mso-width-source:userset;mso-width-alt:5728;width:134pt" width="179"> </colgroup><tbody>

Name One

Address One

City, State, Zip

Name Two

Address Two

City, State, Zip

Name Threee

Address Three

City, State, Zip

<colgroup><col style="mso-width-source:userset;mso-width-alt:5728;width:134pt" width="179"> </colgroup><tbody>
</tbody>

</tbody>

 

[Peter_SSs]

Welcome to the MrExcel board!

It would be helpful to see a small sample, say 4-5 sets, of actual data (with any private info altered) so that we can see exactly what we are dealing with.
My signature block below has info about posting small screen shots of data that can be copy/pasted to test with.

 

[My Aswer Is This]

Does every record have five fields?
If not how do we know when the next record begins?

 

[cartley]

Sorry I tried to post a data example with my question but it looks like it was blocked. All unique data will contain five fields in three separate cells. Hopefully manually typing it in below will do the trick. Thank you!!

A1Name One
A2Address One
A3City One, State One Zip One
A4Name Two
A5Address Two
A6City Two, State Two Zip Two
A7Name Three
A8Address Three
A9City Three, State Three Zip Three

 

[Michael M]

Is it ALWAYS only 3 rows per customer ????

 

[My Aswer Is This]

In your original post it seems as if you want five different fields in each row
So now it seems as if you have all your data in three fields and wants to split it out into five fields.
That could be interesting.
You say you have been using transpose but transpose is not able to split three values in one cell to three different cells.
Or am I missing something?

 

[cartley]

Yes, Michael. Always one 3 per....

 

[cartley]

In your original post it seems as if you want five different fields in each row
So now it seems as if you have all your data in three fields and wants to split it out into five fields.
That could be interesting.
You say you have been using transpose but transpose is not able to split three values in one cell to three different cells.
Or am I missing something?

Transpose works just fine other than it is very tedious to have someone repeat this over and over again for hundreds of records per week.

 

[Peter_SSs]

1. For the City/State/Zip rows (rows 3,6,9,..)
Could we see some actual sample data (say 6-8 rows showing any variations in structure) or else detail about:
- Do any zip codes contain spaces?
- Are the states full state names (ie may contain spaces) or abbreviations (containing no spaces?)
- Is there actually a comma after every city name?


2. Do you actually want the results only in rows 1, 4, 7, .. or (including name) compacted in rows 1, 2, 3, ..?

 

[My Aswer Is This]

Try this:
Since you say transpose works for you.

Sub Transposeme()
Dim Lastrow As Long
Application.ScreenUpdating = False
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long
Dim b As Long
b = 1
    For i = 1 To Lastrow Step 3
        Cells(i, 1).Resize(3, 1).Copy
        Range("B" & b).PasteSpecial xlPasteValues, Transpose:=True
        b = b + 1
    Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub