how do i convert vertical data into horizontal data

[Rod Dakan]

I am not sure that this can be done but I am scanning a list of people,
address, city state and zip
I can get it into excel but haven't figured out how to get into a format
that will allow me to import it into a database.

The data looks like this in the spreadsheet.

Name first name other name
Company
Address (streee city st zip)
Phone
email
other info
even more info

I will have about 4000 names to deal with so some method of transposing all
of this would be a real help.

Even some suggestions as to software that will help would great.

 

[Pete_UK]

Is there a blank row between each record to help identify each block
of data? And if an item is missing, such as email address, will this
have a blank cell, or will the data be bunched up for each person?

Pete

 

[Rod Dakan]

There is a blank row between records, but if data is missing a blank cell is
not there. Dang that will make it harder.

Rod

 

[Pete_UK]

Yes, it will be harder, but if there is a guaranteed blank row between
each record (and no other blank rows) then at least it is possible to
gather the data together for each record on one row. You might then
need to move some of the data horizontally to make room for missing
items of data, like an email address, but I think you can only do this
manually. You will need a macro for the first part.

Pete

 

[Rod Dakan]

Well that is a beginning can you point me to someone who can write the macro?

Thanks

Rod

 

[Pete_UK]

Well, you might get someone posting a macro here if they read the thread, or
you might like to begin a new post in the .programming group.

Hope this helps.

Pete

 

[Gord Dibben]

Give this macro a shot on a copy of your sheet.

May be close to your needs.

Sub ColtoRows()
Dim rng As Range
Dim I As Long
Dim J As Long
Set rng = Cells(Rows.Count, 1).End(xlUp)
J = 1
On Error Resume Next
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 Wed, 27 Feb 2008 08:38:03 -0800, Rod Dakan <Rod