MS Excel 2010 - Name- Address-City, State and Zip in 3 separate rows - want to change to columns

[lgrande]

Reaching out for help after many attempts. I dont think text to columns will work in this situation, but not sure

A2 = Name
A3 = Street Adress
A4 = City, State and Zip

A5 =Then a blank row

A6 = Name
A7 = Street Adress
A8 = City, State and Zip

And so on..


I need to make labels and this format is not going to work.

Unfortunately I have many people in column A, so I am not sure if we can get this.

I would like

Name in Column A
Street address in column B
City State and Zip in Column C

 

[dermie_72]

See if this code helps you at all.

Sub lgrande()
'for http://www.mrexcel.com/forum/excel-...-zip-3-separate-rows-want-change-columns.html
Dim X As Long
Dim LR As Long
X = 2
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LR Step 4
    Cells(i, 1).Resize(4, 1).Copy
    Cells(X, 2).Resize(, 4).PasteSpecial Transpose:=True
    X = X + 1
Next i
Columns("A").EntireColumn.Delete
End Sub

 

[hiker95]

lgrande,

Sample raw data:

Excel 2007
	A	B	C
1
2	Name #1
3	Street Address1
4	City, State and Zip #1
5
6	Name #2
7	Street Address2
8	City, State and Zip #2
9
10	Name #3
11	Street Address3
12	City, State and Zip #3
13
Sheet1

After the fast macro using two arrays in memory:

Excel 2007
	A	B	C
1
2	Name #1	Street Address1	Street Address1
3	Name #2	Street Address2	Street Address2
4	Name #3	Street Address3	Street Address3
5
6
7
8
9
10
11
12
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 09/22/2013
' http://www.mrexcel.com/forum/excel-questions/728129-ms-excel-2010-name-address-city-state-zip-3-separate-rows-want-change-columns.html
Dim a As Variant, b As Variant
Dim i As Long, ii As Long
a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim b(1 To UBound(a, 1), 1 To 3)
For i = 1 To UBound(a, 1) Step 4
  ii = ii + 1
  b(ii, 1) = a(i, 1)
  b(ii, 2) = a(i + 1, 1)
  b(ii, 3) = a(i + 1, 1)
Next i
Range("A2").Resize(UBound(b, 1), UBound(b, 2)) = b
Columns("A:C").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[lgrande]

Thanks for replying. I am getting an error when I try and run the macro. a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row) It is highlighting what is in purple. Any ideas?

Linda

 

[lgrande]

Sorry the error is : Invalid outside procedure

 

[hiker95]

lgrande,

When you respond to someone helping you, it is best to start a reply with your helper's handle/ID. This way it makes it easier to follow what is going on, and, who should be responding.

I did display screenshots of before and after. Were my screenshots an accurate representation of what your real data looks like?

In order to continue:

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Or even better:

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[lgrande]

Thanks Hiker95,

Yes, it is an accurate representation, but I am using 2010 version. I have tried to get a screenshot but it is failing. I will continue to try. I appreciate your help.
lgrande

UPDATE: I got it to work, but column 3 is showing the same info as column 2, instead of city state and zip. Wow..I need to learn more about this. You are awesome. Can the code be tweaked a bit to fix the 3rd column?

 

[hiker95]

lgrande,

I got it to work, but column 3 is showing the same info as column 2, instead of city state and zip.

Sorry about that.

Sample raw data:

Excel 2007
	A	B	C
1
2	Name #1
3	Street Address1
4	City, State and Zip #1
5
6	Name #2
7	Street Address2
8	City, State and Zip #2
9
10	Name #3
11	Street Address3
12	City, State and Zip #3
13
Sheet1

After the updated macro:

Excel 2007
	A	B	C
1
2	Name #1	Street Address1	City, State and Zip #1
3	Name #2	Street Address2	City, State and Zip #2
4	Name #3	Street Address3	City, State and Zip #3
5
6
7
8
9
10
11
12
13
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ReorgDataV2()
' hiker95, 10/01/2013
' http://www.mrexcel.com/forum/excel-questions/728129-ms-excel-2010-name-address-city-state-zip-3-separate-rows-want-change-columns.html
Dim a As Variant, b As Variant
Dim i As Long, ii As Long
a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
ReDim b(1 To UBound(a, 1), 1 To 3)
For i = 1 To UBound(a, 1) Step 4
  ii = ii + 1
  b(ii, 1) = a(i, 1)
  b(ii, 2) = a(i + 1, 1)
  b(ii, 3) = a(i + 2, 1)
Next i
Range("A2").Resize(UBound(b, 1), UBound(b, 2)) = b
Columns("A:C").AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgDataV2 macro.

 

[lgrande]

Thanks so much hiker95 - worked like a charm

lgrande

 

[hiker95]

lgrande,

You are very welcome. Glad I could help.

Thanks for the feedback.

And, come back antime.