SORT REPETITIVE DATA FROM ROWS TO COLUMNS

[Mitch]

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 Moehrbach]

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 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]

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 don’t know how to correct it. Maybe you could further
assist me, if possible.

Thank you in advance.

 

[Mitch]

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

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

Mitch

 

[Otto Moehrbach]

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

 

[Gord Dibben]

Do you need the blanks cells?

If not, select column and F5>Special>Blanks>OK>Edit>Delete>Entire 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

 

[Mitch]

Otto -

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

Mitch

 

[Mitch]

Gord -

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

Mitch

 

[Gord Dibben]

Thanks for the feedback Mitch.

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


Gord

Gord -

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

Mitch

Gord Dibben MS Excel MVP

 

[Mitch]

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