Rearranging a Very poorly constructed list

[Lister]

I have scanned some customer lists into excel via a lengthy process and it
has given me one column of data. the data is of 3 rows Name,address,phone
number. I dont have a header but i can add if i need. I would like to
transpose the data (the second and third lines) to column b and c
respectivley, But for the enitre list which is 1201 rows long...i have tried
a few macros i have found and tried to modify them to just 3 rows but i dont
understand Visual basic very well. I appreciate and give many thanks for all
the help you my give me in advance. Glenn

 

[Jacob Skaria]

Macro or even a formula will do..Before that a quick query?

If it is 3 bits it should be 1200 rows and not 1201. Do you have blank
spaces inbetween?



If this post helps click Yes

 

[Lister]

Yes i have been manually cleaning it up as i go, I have a few entries which
had 4 rows so i have changed those already. I am looking for a macro, but am
unable to produce one myself and was wondering if someone may be so kind as
to furnish me with one that i may copy into the worksheet.

 

[Jacob Skaria]

A simple solution would be ......With your data in Column A starting from
Row1 try the below formula in cell B1. Then copy the formula to C1 and D1.
Then copy down as required....

=INDEX($A:$A,(ROW()-1)*3+COLUMN(A1))

'If you are really looking for a macro post back...

If this post helps click Yes

 

[Lister]

This formula copied the name over to the columns in which i placed the
formula. I was really wanting to bring the address which is below the name to
the next column beside the name (column B)and then move the phone number
which is below the address to beside the address (Column C). The information
i have read today regarding this is called "transposing" or an "array".
Thanks again.

 

[Jacob Skaria]

The formula does exactly what you want...Copy the formula to cell B1. Just
make sure you drag/copy formula to C1 and D1 which will bring address to
column C and phone to column D.

OR else try the macro....

Sub MyMacro()
Dim lngRow As Long, lngNRow As Long
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 3
lngNRow = lngNRow + 1
Range("B" & lngNRow & "" & lngNRow) = _
WorksheetFunction.Transpose(Range("A" & lngRow).Resize(3).Value)
Next
End Sub

If this post helps click Yes

 

[Lister]

Thank You Jacob, your a champion, My understanding of Excel is not good, but
that macro worked perfectly.
Regards Glenn