Automated Transposition

[hotblooded]

Hello All,

I need help transposing some data from one column into rows, as necessary. It's probably easier to show you. I have this:

Mary Smith

123 W Main St

NC

Charlotte NC

Joe Smith

321 Broad St

CA

San Jose Ca


And want this:


Mary Smith 123 W Main St NC Charlotte NC
Joe Smith 321 Broad St CA San Jose Ca


Any ideas?

 

[hiker95]

hotblooded,

Can we have screenshots of your data (before and after)?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

If you are not able to give us screenshots, see below in my Signature block: You can upload your workbook to Box Net

 

[hotblooded]

This is what I have:

Excel Workbook
	A
1	Mary Smith
2
3	123 W Main St
4
5	NC
6
7	Charlotte NC
8
9	Joe Smith
10
11	321 Broad St
12
13	CA
14
15	San Jose Ca
Sheet1

Excel 2010

This is what I want:

Excel Workbook
	G	H	I	J
8	Mary Smith	123 W Main St	NC	Charlotte NC
9	Joe Smith	321 Broad St	CA	San Jose Ca
Sheet1

Excel 2010

I am working in Excel 2010

 

[hiker95]

hotblooded,


Sample raw data:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Mary Smith
2
3	123 W Main St
4
5	NC
6
7	Charlotte NC
8
9	Joe Smith
10
11	321 Broad St
12
13	CA
14
15	San Jose Ca
16
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Mary Smith
2
3	123 W Main St
4
5	NC
6
7	Charlotte NC
8							Mary Smith	123 W Main St	NC	Charlotte NC
9	Joe Smith						Joe Smith	321 Broad St	CA	San Jose Ca
10
11	321 Broad St
12
13	CA
14
15	San Jose Ca
16
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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 04/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=544475
Dim LR As Long, a As Long, NR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
NR = 7
For a = 1 To LR Step 8
  NR = NR + 1
  Cells(NR, "G") = Cells(a, 1)
  Cells(NR, "H") = Cells(a + 2, 1)
  Cells(NR, "I") = Cells(a + 4, 1)
  Cells(NR, "J") = Cells(a + 6, 1)
Next a
Columns("G:J").AutoFit
Application.ScreenUpdating = True
End Sub

Before you run the macro, save your workbook, Save As, a macro enabled workbook.


Then run the ReorgData macro.