Change every group of rows to columns

[Gregorykay]

Hi everyone,
I have groups of cells that are different number of rows long. What differentiates the groups is a space in between them. I would like to be able to change every row from A within a group into a new Colum. For example:
INPUT

ColumnA
Name 1
Address 1
Phone 1

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Name 2
Address 2

<o></o>
Name 3
Address 3
Phone 3
Cell 3

OUTPUT

ColumnA-----------ColumnB-----------ColumnC-----------ColumnD
Name 1--------------Adress 1- ------------Phone 1--
Name 2--------------- Adress 2<o></o>
Name 3--------------Adress 3- ------------Phone 3-------------------Cell 3<o></o>
Please help.<o></o>
<o> </o>
Thanks

 

[hiker95]

Gregorykay,

There are several ways to solve your request, but:

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

 

[Gregorykay]

Thanks!

It looks like this<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Excel Workbook
	A
1	8 Motel
2	4567898888
3	6536738888
4	7366663333
5
6	Hilton
7	6473889384
8
9	Sheraton
10
11	Holiday Inn
12	2338767777
13	4355553333
Sheet1

Excel 2007

<o></o>
I want it to look like this<o></o>

Excel Workbook
	A	B	C	D
1	8 Motel	4567898888	6536738888	7366663333
2	Hilton	6473889384
3	Sheraton
4	Holiday Inn	2338767777	4355553333
Sheet1

Excel 2007

 

[hiker95]

Gregorykay,


Sample raw data in worksheet Sheet1 before the macro:

Excel Workbook
	A
1	8 Motel
2	4567898888
3	6536738888
4	7366663333
5
6	Hilton
7	6473889384
8
9	Sheraton
10
11	Holiday Inn
12	2338767777
13	4355553333
14
Sheet1

After the macro:

Excel Workbook
	A	B	C	D
1	8 Motel	4567898888	6536738888	7366663333
2	Hilton	6473889384
3	Sheraton
4	Holiday Inn	2338767777	4355553333
5
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, 05/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=551431
Dim Area As Range, NR As Long
Application.ScreenUpdating = False
Worksheets("Sheet1").Activate
NR = 0
For Each Area In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    NR = NR + 1
    Range("C" & NR).Resize(, .Rows.Count).Value = Application.Transpose(Area)
  End With
Next Area
Columns("A:B").Delete
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

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


Then run the ReorgData macro.