Moving data across columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

amonroe - 14 Apr 2004 19:19 GMT

Hello Everyone,

I hope someone can help me. I have a file containig contact informatio
with all the data in column 1 i.e. name in cell A:1, complete addres
in cell A:2, phone number in cell A:3.

I need the data in a database format i.e. name in A:1, address in B:1
city in C:1, zip in D:1 and phone in E:1. I have about 1,500 files s
the Macro would have to move down and repeat the process.

Also, since A:2 contains the full address, the macro should also brea
apart this information so that the city, state and zip is in its ow
field.

I may need two separate macros to accomplish this. Does anyone have an
suggestions as to how I would go about accomplishing this task?

Thank you in advance.

A

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Gord Dibben - 14 Apr 2004 19:59 GMT

AM

If your data is consistent...X number of rows each, this macro will transpose
to columns.

Then you'll have to deal with splitting the full addresses up into 3 columns
using Data>Text to Columns on Column B. Your data format will dictate what
you use......de-limited or fixed width.

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
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
'3 in your example
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

End Sub

Gord Dibben Excel MVP

>Hello Everyone,
>
[quoted text clipped - 19 lines]
>---
>Message posted from http://www.ExcelForum.com/

Reply to this Message