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>

 

[Michael M]

@My Aswer Is This
That doesn't split up the city, state and Zip code.
As Peter has already asked, we need to know what seperates the 3 on a consistent basis

 

[My Aswer Is This]

I know Michael. But the original poster said he had been using Transpose and it worked fine for him but took a long time. I'm thinking maybe he does not want them split out.
If he want's them split out that is probable beyond my Knowledge base. I will keep watching this thread.

 

[cartley]

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, ..?

Hi Peter:

To answer your question above, none of the zip codes contain spaces. They are either in the format 85258 or 85258-1234. The states is always abbreviated and it is AZ for all entries. There is a comma after every city name. If everything can be compacted down that is ideal. Thanks for your insight.

 

[Peter_SSs]

.. none of the zip codes contain spaces. They are either in the format 85258 or 85258-1234. The states is always abbreviated and it is AZ for all entries. There is a comma after every city name. If everything can be compacted down that is ideal. Thanks for your insight.

OK, this does allow for other state abbreviations too, though you say all are "AZ".

Test in a copy of your workbook.

Sample data (modified to remove spaces from zip codes & states) & results below.

Sub rearrange()
  Dim a, b, bits
  Dim i As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp))
  ReDim b(1 To UBound(a) / 3, 1 To 5)
  For i = 1 To UBound(a) Step 3
    bits = Split(a(i + 2, 1), ",")
    k = k + 1
    b(k, 1) = a(i, 1)
    b(k, 2) = a(i + 1, 1)
    b(k, 3) = bits(0)
    b(k, 4) = Split(Trim(bits(1)))(0)
    b(k, 5) = Split(Trim(bits(1)))(1)
  Next i
  With Range("C1").Resize(UBound(b), 5)
    .Value = b
    .EntireColumn.AutoFit
  End With
End Sub

Excel Workbook
	A	B	C	D	E	F	G
1	Name One		Name One	Address One	City One	AZ	ZipOne
2	Address One		Name Two	Address Two	City Two	NY	ZipTwo
3	City One, AZ ZipOne		Name Three	Address Three	City Three	AZ	ZipThree
4	Name Two
5	Address Two
6	City Two, NY ZipTwo
7	Name Three
8	Address Three
9	City Three, AZ ZipThree
Sheet2

 

[cartley]

Peter, this works flawlessly. Thank you very very much for your help and everyone else for your input.

 

[Peter_SSs]

Great news! Thanks for letting us know.

 

[My Aswer Is This]

I wish I could understand all this code.

OK, this does allow for other state abbreviations too, though you say all are "AZ".

Test in a copy of your workbook.

Sample data (modified to remove spaces from zip codes & states) & results below.

Sub rearrange()
  Dim a, b, bits
  Dim i As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp))
  ReDim b(1 To UBound(a) / 3, 1 To 5)
  For i = 1 To UBound(a) Step 3
    bits = Split(a(i + 2, 1), ",")
    k = k + 1
    b(k, 1) = a(i, 1)
    b(k, 2) = a(i + 1, 1)
    b(k, 3) = bits(0)
    b(k, 4) = Split(Trim(bits(1)))(0)
    b(k, 5) = Split(Trim(bits(1)))(1)
  Next i
  With Range("C1").Resize(UBound(b), 5)
    .Value = b
    .EntireColumn.AutoFit
  End With
End Sub

Excel Workbook
	A	B	C	D	E	F	G
1	Name One	*	Name One	Address One	City One	AZ	ZipOne
2	Address One	*	Name Two	Address Two	City Two	NY	ZipTwo
3	City One, AZ ZipOne	*	Name Three	Address Three	City Three	AZ	ZipThree
4	Name Two	*	*	*	*	*	*
5	Address Two	*	*	*	*	*	*
6	City Two, NY ZipTwo	*	*	*	*	*	*
7	Name Three	*	*	*	*	*	*
8	Address Three	*	*	*	*	*	*
9	City Three, AZ ZipThree	*	*	*	*	*	*
Sheet2

 

[cartley]

Hi Peter:

I'm hoping you can help with a problem we stated having recently with the amazing code you helped with earlier this year. All of a sudden we began receiving a "Run-time error '9':
Subscript out of range" error when we try and run this macro. The line that it is highlighting is the "b(k,5) line. When i remove that line from the code i get everything to separate into the various columns like it used to except the zip code which is that last line. I appreciate any insight you might have on why we are getting this error. Thanks!

OK, this does allow for other state abbreviations too, though you say all are "AZ".

Test in a copy of your workbook.

Sample data (modified to remove spaces from zip codes & states) & results below.

Sub rearrange()
  Dim a, b, bits
  Dim i As Long, k As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp))
  ReDim b(1 To UBound(a) / 3, 1 To 5)
  For i = 1 To UBound(a) Step 3
    bits = Split(a(i + 2, 1), ",")
    k = k + 1
    b(k, 1) = a(i, 1)
    b(k, 2) = a(i + 1, 1)
    b(k, 3) = bits(0)
    b(k, 4) = Split(Trim(bits(1)))(0)
    b(k, 5) = Split(Trim(bits(1)))(1)
  Next i
  With Range("C1").Resize(UBound(b), 5)
    .Value = b
    .EntireColumn.AutoFit
  End With
End Sub

Sheet2

	A	B	C	D	E	F	G
1	Name One		Name One	Address One	City One	AZ	ZipOne
2	Address One		Name Two	Address Two	City Two	NY	ZipTwo
3	City One, AZ ZipOne		Name Three	Address Three	City Three	AZ	ZipThree
4	Name Two
5	Address Two
6	City Two, NY ZipTwo
7	Name Three
8	Address Three
9	City Three, AZ ZipThree

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:179px;"><col style="width:31px;"><col style="width:89px;"><col style="width:104px;"><col style="width:78px;"><col style="width:37px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Hi Peter:

I'm hoping you can help with a problem we stated having recently with the amazing code you helped with earlier this year. All of a sudden we began receiving a "Run-time error '9':
Subscript out of range" error when we try and run this macro. The line that it is highlighting is the "b(k,5) line. When i remove that line from the code i get everything to separate into the various columns like it used to except the zip code which is that last line. I appreciate any insight you might have on why we are getting this error. Thanks!

Without seeing the data, my best guess is that one or more of your 'name and address blocks' occupies less than 3 rows in the sheet. In that case, not only would that error occur, but you would be unable to rely on the results of that macro anyway as it is reliant on every block using 3 rows.

1. Can you confirm my suspicion?

2. If so, is it the first address line that is (at least sometimes) missing? That is, does every section contain a name row and a City/State/Zip row?

 

[cartley]

Thank you for that insight. This was a 1500 cell workbook they were trying to run the macro on and one address was missing a zip code. As soon as we added that in everything works great once again. Thanks for your help!