I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

This little macro should do what you want. As written, this macro assumes
the data is in Column A and the first name is in A2. It also assumes that
each "piece" of data consists of 3 cells bracketed by a blank cell above it
and below it. This macro puts the product in Columns B:D starting in row 2.
Change these parameters in the code as needed to match your actual data.
HTH Otto
Sub TransposeAll()
Dim Source As Range
Set Source = Range("A2")
Do
Source.Resize(3).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Transpose:=True
Set Source = Source.Offset(4)
Loop Until Source.Value = ""
End Sub

"Mitch" wrote in message
...
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a
column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Thanks. You seem to understand exactly what I am trying to accomplish. I have
no specific knowledge regarding the macro you created for me but, what you
wrote me makes sense. When I ran the macro as written a window pops up and
Transpose:=True is in red. My assumption is that this means the macro bogged
down at this point. I dont know how to correct it. Maybe you could further
assist me, if possible.

Thank you in advance.



"Otto Moehrbach" wrote:

This little macro should do what you want. As written, this macro assumes
the data is in Column A and the first name is in A2. It also assumes that
each "piece" of data consists of 3 cells bracketed by a blank cell above it
and below it. This macro puts the product in Columns B:D starting in row 2.
Change these parameters in the code as needed to match your actual data.
HTH Otto
Sub TransposeAll()
Dim Source As Range
Set Source = Range("A2")
Do
Source.Resize(3).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Transpose:=True
Set Source = Source.Offset(4)
Loop Until Source.Value = ""
End Sub

"Mitch" wrote in message
...
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a
column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Otto-
I took the colon out after TRANSPOSE. Now although the macro runs, all it
does is take the first name and address listed in column A and duplicates it
in column B. It doesn't spread the data across B:D

I know you're close but I don't know enough about macros.

Mitch

"Otto Moehrbach" wrote:

This little macro should do what you want. As written, this macro assumes
the data is in Column A and the first name is in A2. It also assumes that
each "piece" of data consists of 3 cells bracketed by a blank cell above it
and below it. This macro puts the product in Columns B:D starting in row 2.
Change these parameters in the code as needed to match your actual data.
HTH Otto
Sub TransposeAll()
Dim Source As Range
Set Source = Range("A2")
Do
Source.Resize(3).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Transpose:=True
Set Source = Source.Offset(4)
Loop Until Source.Value = ""
End Sub

"Mitch" wrote in message
...
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a
column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Mitch
You're probably falling victim to line wrapping in these messages. That
is always a problem. The line of code that looks like:
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Transpose:=True
must be all on one line. Note that there must be a space before the word
Transpose. Post back and tell us if this works for you. Otto

"Mitch" wrote in message
...
Otto-
I took the colon out after TRANSPOSE. Now although the macro runs, all it
does is take the first name and address listed in column A and duplicates
it
in column B. It doesn't spread the data across B:D

I know you're close but I don't know enough about macros.

Mitch

"Otto Moehrbach" wrote:

This little macro should do what you want. As written, this macro
assumes
the data is in Column A and the first name is in A2. It also assumes
that
each "piece" of data consists of 3 cells bracketed by a blank cell above
it
and below it. This macro puts the product in Columns B:D starting in row
2.
Change these parameters in the code as needed to match your actual data.
HTH Otto
Sub TransposeAll()
Dim Source As Range
Set Source = Range("A2")
Do
Source.Resize(3).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Transpose:=True
Set Source = Source.Offset(4)
Loop Until Source.Value = ""
End Sub

"Mitch" wrote in message
...
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a
column
BLANK CELL NAME ADDRESS CITY, STATE ZIP
BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it
in
one operation instead of a thousand?

Do you need the blanks cells?

If not, select column and F5SpecialBlanksOKEditDeleteEntire Row

Then run this macro.

If you want the blanks, leave them and enter "5" in the "columns desired.
Otherwise, enter "3"

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP


On Fri, 23 Jun 2006 09:55:02 -0700, Mitch
wrote:

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Otto -

Thanks for the help but still no go. However Gord Dibbon posted a mocro that
did exactly what I needed.

Mitch

"Otto Moehrbach" wrote:

This little macro should do what you want. As written, this macro assumes
the data is in Column A and the first name is in A2. It also assumes that
each "piece" of data consists of 3 cells bracketed by a blank cell above it
and below it. This macro puts the product in Columns B:D starting in row 2.
Change these parameters in the code as needed to match your actual data.
HTH Otto
Sub TransposeAll()
Dim Source As Range
Set Source = Range("A2")
Do
Source.Resize(3).Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
Transpose:=True
Set Source = Source.Offset(4)
Loop Until Source.Value = ""
End Sub

"Mitch" wrote in message
...
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a
column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Gord -

An absolute winner! Your macro did exactly what I needed it to do. Thanks a
million

Mitch

"Gord Dibben" wrote:

Do you need the blanks cells?

If not, select column and F5SpecialBlanksOKEditDeleteEntire Row

Then run this macro.

If you want the blanks, leave them and enter "5" in the "columns desired.
Otherwise, enter "3"

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP


On Fri, 23 Jun 2006 09:55:02 -0700, Mitch
wrote:

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?

Thanks for the feedback Mitch.

BTW....what did you do with the blanks?


Gord

On Fri, 23 Jun 2006 15:44:01 -0700, Mitch
wrote:

Gord -

An absolute winner! Your macro did exactly what I needed it to do. Thanks a
million

Mitch

"Gord Dibben" wrote:

Do you need the blanks cells?

If not, select column and F5SpecialBlanksOKEditDeleteEntire Row

Then run this macro.

If you want the blanks, leave them and enter "5" in the "columns desired.
Otherwise, enter "3"

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP


On Fri, 23 Jun 2006 09:55:02 -0700, Mitch
wrote:

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?



Gord Dibben MS Excel MVP

Gord -

Deleted them as per your instruction. Again, thanks a million. This savede
me hours of manual labor. I was prepared to enter the info manually into my
database if not for the workaround.

Mitch

"Gord Dibben" wrote:

Thanks for the feedback Mitch.

BTW....what did you do with the blanks?


Gord

On Fri, 23 Jun 2006 15:44:01 -0700, Mitch
wrote:

Gord -

An absolute winner! Your macro did exactly what I needed it to do. Thanks a
million

Mitch

"Gord Dibben" wrote:

Do you need the blanks cells?

If not, select column and F5SpecialBlanksOKEditDeleteEntire Row

Then run this macro.

If you want the blanks, leave them and enter "5" in the "columns desired.
Otherwise, enter "3"

Sub ColtoRows()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
nocols = InputBox("Enter Number of Columns Desired")

For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Exit Sub
End Sub


Gord Dibben MS Excel MVP


On Fri, 23 Jun 2006 09:55:02 -0700, Mitch
wrote:

I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL

I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?



Gord Dibben MS Excel MVP