Convertingg a single Column of Information to Database

[candeos]

Hello

I have a bunch of information that is currently in a single column and 3,441 rows of an excel spreadsheet It looks like this"

Record 1 Name
Record 1 Address
Record 1 City
Record 1 State
Record 1 Zip
{space}
Record 2 Name
Record 2 Address
...
...

How do I convert this to look like this across multiple columns and rows?

Record 1 Name Record 1 Address Record 1 City ....
Record 2 Name Record 2 Address Record 2 City...

I tried using a macro, but I dont know how to make it search for a space and write the data to the next row in the final table.

Thanks in advance for the help!

Dave

 

[xlbo]

A: Is there always a space between the customers changing ?? (ie record 1 to record 2)
B: Is there the same no. of records for each customer ie does each "record 1" have all the required data or might some be missing ??

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[candeos]

Geoff

There is always a space. I looked at the data and sometimes there is not data in the last field of the row ( converted to columns)

The column looks like this

Abby
781-xxx-xxxx
Fax: 781-xxx-xxxx
Memorial Hall
500 Main St
2351 ( note we need to a a leading zero as it got cut off in the conversion so that it says ( 02351)
Accu
M-F: 8:30a-4:30p
{space}
Activity
978-xxx-xxxx
Fax: 978-xxx-xxxx
Salde Hall
100 Main St.
1720 ( add the leading 0)
Opie
{space}

The last field is the one that is sometimes missing.

Thanks for the help!

Dave

 

[xlbo]

ok - so assuming 9 fields is the norm and that your data is in col A, starting in A1 on sheet1

dim lRow as long, oSet as integer, uRow as long

lRow = cells(65536,1).end(xlup).row
oSet = 1
uRow = 2

application.screenupdating = false

For i = 1 to lRow
 if cells(i,1).text = "" then
  'New record starts
  oSet = 1
  uRow = uRow + 1
 else
   with activesheet
     sheets("Sheet2").cells(uRow,oSet).value = .cells(i,1).value
   end with
 end if
next

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question