Transposing data

[glflame24]

I have a set of data in Excel containing address information in the following format:

Name	Address1	Address2	City, State ZIP
John Doe	123 Ave	Apt 1	NY, NY 10001
Jane Doe	321 Ave	Apt 1	NY, NY 10001

<colgroup><col span="4"></colgroup><tbody>
</tbody>

I have several hundred names/addresses in this format that I need compiled in 1 column in the following format:

John Doe

123 Ave

Apt 1

NY, NY 10001

Jane Doe

321 Ave

Apt 1

NY, NY 10001

<colgroup><col></colgroup><tbody>
</tbody>

Any help is appreciated. Thanks!

 

[hiker95]

glflame24,

Welcome to the MrExcel forum.

If I understand you correctly, then here is a macro solution for you to consider, that is based on your flat text displays, that uses two arrays in memory, and, should be very fast.

I assume that the City, State ZIP field will contain the two character abbreviations for the city, and, for the state.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Address1	Address2	City, State ZIP
2	John Doe	123 Ave	Apt 1	NY, NY 10001
3	Jane Doe	321 Ave	Apt 1	NY, NY 10001
4
5
6
7
8
9
10
11
12
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Address1	Address2	City, State ZIP			John Doe
2	John Doe	123 Ave	Apt 1	NY, NY 10001			123 Ave
3	Jane Doe	321 Ave	Apt 1	NY, NY 10001			Apt 1
4							NY, NY
5							10001
6
7							Jane Doe
8							321 Ave
9							Apt 1
10							NY, NY
11							10001
12
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.

Sub ReorgData()
' hiker95, 02/16/2017, ME991640
Dim a As Variant, i As Long, c As Long, s
Dim o As Variant, j As Long
With ActiveSheet
  a = .Range("A1").CurrentRegion
  ReDim o(1 To (UBound(a, 1) - 1) * (UBound(a, 2) + 2))
  For i = 2 To UBound(a, 1)
    For c = 1 To UBound(a, 2) - 1
      j = j + 1: o(j) = a(i, c)
    Next c
    s = Split(a(i, 4), " ")
    j = j + 1: o(j) = s(0) & " " & s(1)
    j = j + 1: o(j) = s(2)
    j = j + 1
  Next i
  .Columns("G").ClearContents
  .Range("G1").Resize(UBound(o)) = Application.Transpose(o)
  .Columns("G").AutoFit
End With
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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[FlameRetired]

Another way if formula solution is OK.

Excel 2012
	A	B	C	D	E	F
1	Name	Address1	Address2	City, State ZIP
2	John Doe	123 Ave	Apt 1	NY, NY 10001		John Doe
3	Jane Doe	321 Ave	Apt 1	NY, NY 10001		123 Ave
4						Apt 1
5						NY, NY 10001
6
7						Jane Doe
8						321 Ave
9						Apt 1
10						NY, NY 10001
Sheet1

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX($A$2:$E$3&"",CEILING(ROWS($2:2)/5,1),MOD(ROWS($2:2)-1,5)+1),"")

 

[hiker95]

glflame24,

Here is another macro for you to consider, that is based on your flat text displays, and, that uses two arrays in memory, and, should be very fast.

Sample raw data, and, results:

Excel 2007
	A	B	C	D	E	F	G
1	Name	Address1	Address2	City, State ZIP			John Doe
2	John Doe	123 Ave	Apt 1	NY, NY 10001			123 Ave
3	Jane Doe	321 Ave	Apt 1	NY, NY 10001			Apt 1
4							NY, NY 10001
5
6							Jane Doe
7							321 Ave
8							Apt 1
9							NY, NY 10001
10
Sheet1

With the same instructions as my last reply.

Sub ReorgData_V2()
' hiker95, 02/16/2017, ME991640
Dim a As Variant, i As Long, c As Long
Dim o As Variant, j As Long
With ActiveSheet
  a = .Range("A1").CurrentRegion
  ReDim o(1 To (UBound(a, 1) - 1) * (UBound(a, 2) + 1))
  For i = 2 To UBound(a, 1)
    For c = 1 To UBound(a, 2)
      j = j + 1: o(j) = a(i, c)
    Next c
    j = j + 1
  Next i
  .Columns("G").ClearContents
  .Range("G1").Resize(UBound(o)) = Application.Transpose(o)
  .Columns("G").AutoFit
End With
End Sub

Then run the ReorgData_V2 macro.