Move data to list format

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?
0
anonymous (74722)
5/30/2004 5:46:03 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
356 Views

Similar Articles

[PageSpeed] 32

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" <anonymous@discussions.microsoft.com> wrote in message
news:FA3B34EA-49F7-4047-B9B1-B543524ACF91@microsoft.com...
> 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?


0
bob.phillips1 (6510)
5/30/2004 6:02:30 PM
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


--
Regards
Frank Kabel
Frankfurt, Germany


sagesearcher wrote:
> 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?

0
frank.kabel (11126)
5/30/2004 6:17:21 PM
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

0
anonymous (74722)
5/30/2004 8:51:03 PM
Oops!!  My previous email should have been addressed to Frank.  Thanks!!
0
anonymous (74722)
5/30/2004 9:11:02 PM
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?

--
Regards
Frank Kabel
Frankfurt, Germany


sagesearcher wrote:
> 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:

0
frank.kabel (11126)
5/30/2004 9:20:19 PM
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.
0
anonymous (74722)
5/30/2004 9:36:03 PM
Reply:

Similar Artilces:

Excel Regional Date Format Options
A client of ours in NZ is complaining that date format options for English (New Zealand) have changed from older versions of excel (they are using 2003) Some of their spreadsheets have dates formatted as dd-mmm-yy, mmm-yy and dddd,dd,mmm but these options do not exist anymore. Is there anyway to add options to this list without using the custom format option? Thanks, Jesse I just compared the Excel 97 and Excel 2003 built-in date formats and they are mostly unchanged. 2003 has a few more but I don't think there were any subtractions. The formats dd-mmm-yy and mmm-yy are righ...

Auto fill and drop down lists
The fields are Rank and Rank Sort (I cant do this alpabetically as a the ranks (Military) don't run in alphabetical order. The idea is this: The form (Pers Details Form) contains personal details Name, Number etc from the Pers Details Table. There will be box where I can select the rank from a dropdown list. The Rank details are contained in a seperate table (Rank Table) containing a Rank and Rank Sort column. In the Pers Details Form I can create a record, enter details but I want to have a drop down box to choose the rank from. Once chosen I would like the rank to show (but ...

Price list 02-23-06
Does anyone know how to default a quote to a particular price list, or can I remove this mandatory field? Tks. Ian Hi Ian, You could write a script at the OnLoad Event of the Quote Form. var CRM_FORM_TYPE_CREATE = 1; var CRM_FORM_TYPE_UPDATE = 2; switch (crmForm.FormType) { case CRM_FORM_TYPE_CREATE: alert("This is a create form."); //insert the PriceList GUID into the Price List look up field here. break; case CRM_FORM_TYPE_UPDATE: break; } Hope this helps. -- Manisha Powar (Madhusudanan) Program Manager Microsoft Dynamics - CRM This post...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

Moving Email Domain off of Exchange #2
Good afternoon, I am in the process of moving one of our clients off of our Exchange server and on to a third party email host. I have removed there domain from the default recepient policy, and also disabled and unattached or deleted their mailboxes. I have repointed there MX record to point to the new host etc. The problem is when I try to email them from our network my exchange server still thinks it is responsible for handling the mail for the domain and does not try to send it out of the domain. I get a 5.1.1 e-mail account does not exist in this organization. How do I let Exchange ...

List box with available queries question
I have a list box control on a form and want the list box to display all the queries within the database. The following code is what I have so far but it does not work. Any help is appreciated. SELECT [Name] FROM MSysObjects WHERE [TYPE] = 5 and LEFT([Name],1) <> "-" ORDER BY [Name]; "Billy B" <BillyB@discussions.microsoft.com> wrote in message news:F47BB77B-7B66-4860-8954-F4FE32FE7C3C@microsoft.com... >I have a list box control on a form and want the list box to display all >the > queries within the database. The following code is what...

Force command to run for each computer in txt file list of compute
I have a few hundred computers that haven't been defragged in years. I was wondering if rather than doing it manually, if someone had a script where if i just put all the computer names in a txt file, it would iterate through the list and perform a "defrag c: -f" on them all from my workstation. The workstations all have the same local administrator password, so the process would be the same for each. I just dont know how to write scripts. I know if i were going to do this manually from my computer I would run the command below for each ComputerName psexec ...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

Conditional formatting help #4
My problem is that, that i want to ignore blank i mean i had set a conditional formatting say A B C D 24.9 25.9 25 25.8 22.6 23.4 22.5 23.3 If value in ColA is less than value in ColC, cell A1 is shaded blue OR if value in ColB is greater than value in ColD, cell B1 shaded blue. I have done above formatting but my problem is that if i dont enter anything in colC then also colA is shaded in blue similarly if i dont enter any value in colD then also col B is shaded.I mean i want to ignore the blank.I need , if col C is blank then the Col A must be normal .& if col D is blank & i ent...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

VFW, webcam, image format
Hello, I'm trying to grab pictures from a webcam with Video For Windows (VFW). The source code I use is the following: HWND _capwnd = capCreateCaptureWindow("Capture",WS_POPUP,0,0,1,1,0,0); capDriverConnect(_capwnd,0); capGrabFrame(_capwnd); capEditCopy(_capwnd); OpenClipboard(NULL); HBITMAP hBmp = (HBITMAP)GetClipboardData(CF_BITMAP); CloseClipboard(); BITMAP bmp; GetObject(hBmp, sizeof(BITMAP), &bmp); int size = bmp.bmWidth*bmp.bmHeight*bmp.bmWidthBytes; unsigned char* data = new unsigned char[size]; GetBitmapBits(hBmp,size, data); ...

Linking Drop-down list to worksheets
I need 20 dropdown list on the worksheet. some of drop-down lists has the same values. I need a list thats has one set of names and worksheet has another set of names. Here is a example want I need. LIST names Worksheet names Red - Black 770rb Black-White 770bw My list will have 15 items in each list. When user click on the color it would take them to that worksheet. What is best way to do this? ...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

inbox contents moved to contacts folder
I recently had a user of Microsoft Outlook 2003 report a strange problem to me. She stated when she opened her inbox all the emails were gone and upon investigation she found them all in her contacts folder. Is this possible/ how can it happen and how do we get them back? Thanks, Pat she probably accidently dragged them to the contacts folder. if they aren't converted to contacts, drag them back. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/ou...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

arranging items in a list control
hi the code below is suppose to move a selected item in a list control upwards, it works for the first column have not the second one(it only deletes the items in the 2nd column), what do you think is wrong Thanks for your help void CTestingDlg::OnUp() { int nIndex; CString strCommand,strCommand2; nIndex = m_cListCtrl.GetNextItem(-1, LVNI_SELECTED); ASSERT(-1 != nIndex); strCommand = m_cListCtrl.GetItemText(nIndex, 0); strCommand2 = m_cListCtrl.GetItemText(nIndex,1); m_cListCtrl.DeleteItem(nIndex); nIndex = m_cListCtrl.InsertItem(nIndex - 1, strCommand); m_cListCtrl...

MM from Word in HTML Format
I am using MS Office 2002. When I do a mail-merge from MS Word to email, it works fine as long as I use 'Plain Text' as the format. It accesses Outlook and sends the messages. However, if I use HTML format, it acts as though it is sending something, but the messages never get sent. Does anyone know what my problem might be? Brian, I seem to have the same problem in Offic 2003. When I do an email merge from Word, the screen flickers at the end but there is no record of the emails having been sent in Outlook, i.e., they are not in the Sent folder. I've tried looking around...

How do I format a cell to auto date
I would like to have a date automatically enter itself each time I open the spreadsheet. Is there a way to do this? Any help would be appreciated. Thanks Hi George Use a function like this one for todays date =TODAY() -- Regards Ron de Bruin http://www.rondebruin.nl "George" <George@discussions.microsoft.com> wrote in message news:7EF21130-EBD9-41C2-8CB5-5723BE40CBB1@microsoft.com... >I would like to have a date automatically enter itself each time I open the > spreadsheet. Is there a way to do this? Any help would be appreciated. > Thanks ...

Remove Data Format Change
I have a program that puts a database's data into an excel spreadsheet. The problem is that Excel is constantly changing the numbers to dates, which corrupts the data. I know I can switch the column data type to Text and that will fix it, but the problem is that when the data is written to the spreadsheet it overwrites that column data type and Excel does the formatting that changes my data. How can I turn this off so Excel stops trying to recognize data types and change their format? Please help because this is causing major problems for us. Thanks, ACFalcon >>I have a ...

Data
I can not see the entires I made for the past month. What can I do? In microsoft.public.money, Magnus wrote: >I can not see the entires I made for the past month. What >can I do? I don't know what would cause that. http://support.microsoft.com/default.aspx?scid=kb;en-us;182608 describes using -s switch. That is a simple safe thing to try. http://support.microsoft.com/default.aspx?scid=kb;en-us;274584 describes salv.exe. These may not solve your problem, but they are what I would do. I would also review my backup file situation. ...