Move data to list format

S

sagesearcher

I have a customer list downloaded into Excel. I want to move customer name, address, state, zip, etc fields into columns so that I can import them into a database. Each customer, for example, is separated by about eleven rows. Can this be done?
 
B

Bob Phillips

As long as they are separated by 11 rows, this does it

Sub testloop()
Dim cRows As Long
Dim i As Long
Dim j As Long
Dim agtname As String

cRows = Range("A" & Rows.Count).End(xlUp).Row
With Worksheets("Sheet2")
For i = 1 To cRows Step 11
For j = 1 To 11
.Cells((i - 1) \ 11 + 1, j).Value = Cells(i + j - 1,
"A").Value
Next j
Next i
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

sagesearcher said:
I have a customer list downloaded into Excel. I want to move customer
Click to expand...
name, address, state, zip, etc fields into columns so that I can import them
into a database. Each customer, for example, is separated by about eleven
rows. Can this be done?
 
F

Frank Kabel

Hi
and in addition to Bob's macro solution a formula:
Assumptions:
- each customer data record consists EXACTLY of 11 rows
- all data is in column A of sheet 1

Now enter the following formula in cell A1 of a DIFFERENT sheet:

=OFFSET('sheet1'!$A$1,COLUMN()-1+(ROW()-1)*11,0)
and copy this formula 10 columns to the right and as far down as
required.

After this you may select the entire range, copy the range and goto
'Edit - Paste Special' and choose 'Values' to remove the formulas
 
S

sagesearcher

Thanks to you both! Bob, the formula worked beautifully for cells in the same column. Can you tell me how to adjust the formula to extract the data from the following format. I am knowledgable enough to edit the formula for beginning cell reference. I have extracted company name, address, and city. I need state, zip, and contact name. Thanks!!!
Client: 1419 StartDate: 11/01/2001 Billing: COMPANY USA
COMPANY USA Address: ATTENTION: ACCOUNTS PAYABLE
Address: 123 APPLETREE STREET P. O. BOX 12548

JACKSONVILLE
JACKSONVILLE FL 32254 Contact: NORMAN WILDS
Phone: (000)000-000 Phone: (000)000-0000 Fax
Status:
() - - PERMANENT

Contact: NORMAN, PHILLIP (904) 693-1354 Title
 
F

Frank Kabel

Hi
- is this information in ONE single cell?
- Do you always have an identifier before the information like
'address:' or 'Contact:
- Does this information has always the same order?
 
S

sagesearcher

Sorry format did not carry over. Each piece of info is in one cell. State and zip do not have identifiers--contact does. Their addresses are state=E12, zip=F12 and contact=H12. I edited your first formula as comapny was at C8--not A1. The info roughly has the same order. I have to do a little editing. Hope that helps!! Thanks again.
 
Top