i have a list of data. all the info is in column a. some info has three
lines some four, some may have more. i need a macro of some sort that would
take all of the data and put it into separate colums. an example is this:

name of company
23 Gates Ave
somewhere, ny 11522
516-xxx-xxxx
accounting

Ruthizer Scott Cpa
30 Gates Ave
somewhere, ny 11552
212-xxx-xxxx


NEEDS TO LOOK LIKE THIS

name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx
Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx


OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN BE
ALL IN ONE COLUMN. PLEASE HELP

Try this:

Sub spread_um()
n = Cells(Rows.Count, "A").End(xlUp).Row
j = 2
k = 1
For i = 1 To n
Cells(k, j).Value = Cells(i, 1).Value
j = j + 1
If Cells(i, 1).Value = "" Then
j = 2
k = k + 1
End If
Next
End Sub

--
Gary''s Student - gsnu200771


"JOSEPH WEBER" wrote:

> i have a list of data. all the info is in column a. some info has three
> lines some four, some may have more. i need a macro of some sort that would
> take all of the data and put it into separate colums. an example is this:
>
> name of company
> 23 Gates Ave
> somewhere, ny 11522
> 516-xxx-xxxx
> accounting
>
> Ruthizer Scott Cpa
> 30 Gates Ave
> somewhere, ny 11552
> 212-xxx-xxxx
>
>
> NEEDS TO LOOK LIKE THIS
>
> name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx
> Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552 212-xxx-xxxx
>
>
> OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN BE
> ALL IN ONE COLUMN. PLEASE HELP
>
>
>

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 2 Step -1

If .Cells(i, TEST_COLUMN).Value = "" Then

.Rows(i).Delete
ElseIf .Cells(i - 1, TEST_COLUMN).Value <> "" Then

.Cells(i - 1, TEST_COLUMN).Value = .Cells(i - 1,
TEST_COLUMN).Value _
& " " & .Cells(i, TEST_COLUMN).Value
.Rows(i).Delete
End If
Next i

End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"JOSEPH WEBER" <JOSEPH (E-Mail Removed)> wrote in message
news:C23060DB-1CEB-42DF-8B6A-(E-Mail Removed)...
>i have a list of data. all the info is in column a. some info has three
> lines some four, some may have more. i need a macro of some sort that
> would
> take all of the data and put it into separate colums. an example is this:
>
> name of company
> 23 Gates Ave
> somewhere, ny 11522
> 516-xxx-xxxx
> accounting
>
> Ruthizer Scott Cpa
> 30 Gates Ave
> somewhere, ny 11552
> 212-xxx-xxxx
>
>
> NEEDS TO LOOK LIKE THIS
>
> name of company 23 Gates Ave somewhere, ny 11522 516-xxx-xxxx
> Ruthizer Scott Cpa 30 Gates Ave somewhere, ny 11552
> 212-xxx-xxxx
>
>
> OBVIOUSLY WITH THE LAST COLUMN IN THERE AS WELL. CITY STATE AND ZIP CAN
> BE
> ALL IN ONE COLUMN. PLEASE HELP
>
>
>