Excel formatting #3

Could someone please help with a macro or any other suggestion to format an
Excel sheet. Basically data is downloaded from a bank every week and the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line 8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one data 
set that is for the record on line 1 to 7. My question is how can I set it 
to replicate so that it replicates for line 8-14 and then 15-21 and so on. 
The source formatting remains the same except that the total length of the 
file( no of records) changes every week.


Thanks very much for your help.

Regards

Sam



0
s_commar (17)
12/7/2006 3:53:35 PM
excel 39879 articles. 2 followers. Follow

4 Replies
428 Views

Similar Articles

[PageSpeed] 11

Sam,
I have had to do this before with a downloaded file from a bank.  I needed 
it to convert dates from text and the dollar amounts as currency. 
Basically, I just had the macro recorder watch me format my first record set 
and go to the beginning of the next record set, turned off the recorder, and 
assigned the macro to a good letter combo (like alt+z).  Then I was able to 
just sit there and hold down alt+z without lifting my fingers off the 
keyboard until I was approaching the end of my recordset, at which point I 
let go of the z (but not alt) and deliberately hit the z once for each 
remaining record set until I reached the end.  I was dealing with hundreds 
and even thousands of transactions per client for about 40 clients each 
month, but this method took mere seconds each and every time.

"Sam Commar" <s_commar@hotmail.com> wrote in message 
news:el87pmhGHHA.3616@TK2MSFTNGP06.phx.gbl...
> Could someone please help with a macro or any other suggestion to format 
> an
> Excel sheet. Basically data is downloaded from a bank every week and the
> size of the file can vary but the formatting is constant. That is each
> record is 7 lines. So first record is line1-7, second record is line 8-14,
> third record is line 15-21 and so on.
>
> I need to format this data and basically I can record a macro for one data 
> set that is for the record on line 1 to 7. My question is how can I set it 
> to replicate so that it replicates for line 8-14 and then 15-21 and so on. 
> The source formatting remains the same except that the total length of the 
> file( no of records) changes every week.
>
>
> Thanks very much for your help.
>
> Regards
>
> Sam
>
>
> 


0
12/7/2006 5:31:34 PM
Wow WOW WOW
IF that works nothign like it. I am trying it now.

Thanks a lot.
Sam
"KC Rippstein" <kcrippstein@hotmail.com> wrote in message 
news:OzKzLWiGHHA.4116@TK2MSFTNGP05.phx.gbl...
> Sam,
> I have had to do this before with a downloaded file from a bank.  I needed 
> it to convert dates from text and the dollar amounts as currency. 
> Basically, I just had the macro recorder watch me format my first record 
> set and go to the beginning of the next record set, turned off the 
> recorder, and assigned the macro to a good letter combo (like alt+z). 
> Then I was able to just sit there and hold down alt+z without lifting my 
> fingers off the keyboard until I was approaching the end of my recordset, 
> at which point I let go of the z (but not alt) and deliberately hit the z 
> once for each remaining record set until I reached the end.  I was dealing 
> with hundreds and even thousands of transactions per client for about 40 
> clients each month, but this method took mere seconds each and every time.
>
> "Sam Commar" <s_commar@hotmail.com> wrote in message 
> news:el87pmhGHHA.3616@TK2MSFTNGP06.phx.gbl...
>> Could someone please help with a macro or any other suggestion to format 
>> an
>> Excel sheet. Basically data is downloaded from a bank every week and the
>> size of the file can vary but the formatting is constant. That is each
>> record is 7 lines. So first record is line1-7, second record is line 
>> 8-14,
>> third record is line 15-21 and so on.
>>
>> I need to format this data and basically I can record a macro for one 
>> data set that is for the record on line 1 to 7. My question is how can I 
>> set it to replicate so that it replicates for line 8-14 and then 15-21 
>> and so on. The source formatting remains the same except that the total 
>> length of the file( no of records) changes every week.
>>
>>
>> Thanks very much for your help.
>>
>> Regards
>>
>> Sam
>>
>>
>>
>
> 


0
s_commar (17)
12/7/2006 6:10:11 PM
Hi Somehow that did not work. It keeps trying to update the first set of my 
records. My destination record set is 2 lines. Was your more that one line.

Thanks
Sam
"Sam Commar" <s_commar@hotmail.com> wrote in message 
news:e8Yc1riGHHA.4804@TK2MSFTNGP03.phx.gbl...
> Wow WOW WOW
> IF that works nothign like it. I am trying it now.
>
> Thanks a lot.
> Sam
> "KC Rippstein" <kcrippstein@hotmail.com> wrote in message 
> news:OzKzLWiGHHA.4116@TK2MSFTNGP05.phx.gbl...
>> Sam,
>> I have had to do this before with a downloaded file from a bank.  I 
>> needed it to convert dates from text and the dollar amounts as currency. 
>> Basically, I just had the macro recorder watch me format my first record 
>> set and go to the beginning of the next record set, turned off the 
>> recorder, and assigned the macro to a good letter combo (like alt+z). 
>> Then I was able to just sit there and hold down alt+z without lifting my 
>> fingers off the keyboard until I was approaching the end of my recordset, 
>> at which point I let go of the z (but not alt) and deliberately hit the z 
>> once for each remaining record set until I reached the end.  I was 
>> dealing with hundreds and even thousands of transactions per client for 
>> about 40 clients each month, but this method took mere seconds each and 
>> every time.
>>
>> "Sam Commar" <s_commar@hotmail.com> wrote in message 
>> news:el87pmhGHHA.3616@TK2MSFTNGP06.phx.gbl...
>>> Could someone please help with a macro or any other suggestion to format 
>>> an
>>> Excel sheet. Basically data is downloaded from a bank every week and the
>>> size of the file can vary but the formatting is constant. That is each
>>> record is 7 lines. So first record is line1-7, second record is line 
>>> 8-14,
>>> third record is line 15-21 and so on.
>>>
>>> I need to format this data and basically I can record a macro for one 
>>> data set that is for the record on line 1 to 7. My question is how can I 
>>> set it to replicate so that it replicates for line 8-14 and then 15-21 
>>> and so on. The source formatting remains the same except that the total 
>>> length of the file( no of records) changes every week.
>>>
>>>
>>> Thanks very much for your help.
>>>
>>> Regards
>>>
>>> Sam
>>>
>>>
>>>
>>
>>
>
> 


0
s_commar (17)
12/7/2006 6:28:36 PM
Sorry, Sam, my idea won't work for your application.  I was not copying and 
pasting, just formatting and moving down, formatting and moving down.
The macro jlepack wrote should work beautifully and is much more intuitive 
anyway.

"Sam Commar" <s_commar@hotmail.com> wrote in message 
news:eTqLI2iGHHA.536@TK2MSFTNGP02.phx.gbl...
> Hi Somehow that did not work. It keeps trying to update the first set of 
> my records. My destination record set is 2 lines. Was your more that one 
> line.
>
> Thanks
> Sam
> "Sam Commar" <s_commar@hotmail.com> wrote in message 
> news:e8Yc1riGHHA.4804@TK2MSFTNGP03.phx.gbl...
>> Wow WOW WOW
>> IF that works nothign like it. I am trying it now.
>>
>> Thanks a lot.
>> Sam
>> "KC Rippstein" <kcrippstein@hotmail.com> wrote in message 
>> news:OzKzLWiGHHA.4116@TK2MSFTNGP05.phx.gbl...
>>> Sam,
>>> I have had to do this before with a downloaded file from a bank.  I 
>>> needed it to convert dates from text and the dollar amounts as currency. 
>>> Basically, I just had the macro recorder watch me format my first record 
>>> set and go to the beginning of the next record set, turned off the 
>>> recorder, and assigned the macro to a good letter combo (like alt+z). 
>>> Then I was able to just sit there and hold down alt+z without lifting my 
>>> fingers off the keyboard until I was approaching the end of my 
>>> recordset, at which point I let go of the z (but not alt) and 
>>> deliberately hit the z once for each remaining record set until I 
>>> reached the end.  I was dealing with hundreds and even thousands of 
>>> transactions per client for about 40 clients each month, but this method 
>>> took mere seconds each and every time.
>>>
>>> "Sam Commar" <s_commar@hotmail.com> wrote in message 
>>> news:el87pmhGHHA.3616@TK2MSFTNGP06.phx.gbl...
>>>> Could someone please help with a macro or any other suggestion to 
>>>> format an
>>>> Excel sheet. Basically data is downloaded from a bank every week and 
>>>> the
>>>> size of the file can vary but the formatting is constant. That is each
>>>> record is 7 lines. So first record is line1-7, second record is line 
>>>> 8-14,
>>>> third record is line 15-21 and so on.
>>>>
>>>> I need to format this data and basically I can record a macro for one 
>>>> data set that is for the record on line 1 to 7. My question is how can 
>>>> I set it to replicate so that it replicates for line 8-14 and then 
>>>> 15-21 and so on. The source formatting remains the same except that the 
>>>> total length of the file( no of records) changes every week.
>>>>
>>>>
>>>> Thanks very much for your help.
>>>>
>>>> Regards
>>>>
>>>> Sam
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
12/7/2006 8:08:04 PM
Reply:

Similar Artilces:

report writer #3
I have installed project management and fixed asset modules along with the default modules of Great Plains version 8. The report writer option does not seem to be working after this I resinstalled the whole application today and found report writer working after creating a sample company. On installing project management, the problem continues to persist I am using windows 2000 server. I have also installed service pack 2 of version 8 GP Anybody else facing the same problem Dear Krishna, Can you Specify more details about what do you mean by report writer options does not seem to workin...

Excel Merge
I have been given an assignment to do a "mail merge" in Excel. The names and addresses are in a document in Excel and the person does not want to have to have the information typed again. Is this possible? I believe a mail merge would have to be done in Word or Access. By the way, I am using a Mac. Thanks for your help. You may want to read some tips for mailmerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. cburke@mainelegal.net wrote: > > I ...

WSS 3 on SBS2003: SP2 break users authorization... :-(
I installed WSS3 "syde by syde" on a new SBS2003 server, leaving (how suggested me by Larry & Lanwench) WSS2 running. All worked fine but yesterday windows update install the WSS3 SP2 and the WSS2 SP3 on server. After install, the new wss3 site is no more accessible , companyweb work without problem. Trying to access the site (http://servername:6000) , i receive this error: HTTP 401.1 Error - Operation not authorized. Access deny because not valid credentials (bad translations from Italian!). I tried to enable anonymous access and add domani users and everyon...

Printed Quote
Curious is anyone has any samples of a printed quote using SRS? What other options for printed quotes are there? Hi, Our Advanced quote and order processing module allows you to quickly and easily create and print quotes in Microsoft CRM. Regards, David Smooha 516 609-5010 http://www.dynamicscrmaddons.com/AdvancedQuotes.asp "dmn8" wrote: > Curious is anyone has any samples of a printed quote using SRS? > > What other options for printed quotes are there? ...

Opening excel file in new window
When an excel is already open in a window and I open a new excel file it opens in the same window. How do I make it open in a new window? You can't. Excel has two windows only; Excel and the VB editor. You may be able to open another instance of Excel though, with some Windows trick or by code. But that is spending a lot of power just for an extra window. HTH. best wishes Harald "Ravi" <Ravi@discussions.microsoft.com> skrev i melding news:B53F2A94-CE10-41F7-A3B0-D472317D527C@microsoft.com... > When an excel is already open in a window and I open a new excel file it ...

How to map a shortcut key to a routine in COM AddIn for Excel 2007
1. Background: I have a COM addins developed in Visual basic 6.0 for EXCEL 2007. The COM addin has its toolbar visible. It exposes customized 'Copy' as a button on the toolbar, which will call MyCopyAction routine in the COM addin. 2. What I want: I want to map a shortcut key for the customized 'copy' operation, which indeed invoke the same routine as that on toolbar, i.e. MyCopyAction. 3. What I tried in the COM addin: I added the following line in Private Sub AddinInstance_OnStartupComplete(custom() As Variant) .... Application.OnKey "+^c&...

Mail merging from Excel to Publisher or Word (office XP)
I am a teacher in a Sheffield UK school teaching GCSE. Part of the requirement this year is to mail merge into either Publisher or Word from an Excel spreadsheet. Did the same last year with no problem (office 2000) but with Office XP now have strange results. Mail merged figures (results of formula) now unexpectedly develop either 4 decimal places when merged to Word, or up to eight in Publisher. i.e 14.67 goes to 14.6667 or 14.66666667, This in spite of formating to two decimal places and/or roundup. My solution is to manually convert the numbers to text but this can't be right...

Merging excel files
Hello everyone, I have one excel file for every day of the month since June 2003. I would like to create one file per month and merge the daily file into it...is there another way to do it other than copy/paste? Please help there is a lot of files what are the names of your files "Raul V" <pacificinet@aol.com> wrote in message news:0fa401c377e3$026dd5e0$a601280a@phx.gbl... > Hello everyone, I have one excel file for every day of the > month since June 2003. I would like to create one file per > month and merge the daily file into it...is there another > way t...

conditional formatting #23
this is probably a basic question, but here goes... I have a worksheet where staff will be slowly filling in dates into one column and I want Excel to automatically add into another column the date that occurs 13 days after the original date. I want the cells in the second column to remain empty until a date is entered by staff into the first column - how do I do that? EXAMPLE: say my staff on Monday enter that day's date (4-27-09) into the first cell in row 2 (row 2/column A) and I want Excel to automatically enter the date that will occur 14 days later (5-11-09) into the 4th cell i...

How do I produce a mailing address list in excel in comma format?
I am trying to send a mailing list to another business. I was asked to send it in excel and in comma format. How can I do this? Hi save your file as '*.csv' file -- Regards Frank Kabel Frankfurt, Germany "frojeff" <frojeff@discussions.microsoft.com> schrieb im Newsbeitrag news:827653C1-2F5C-40A2-9517-26CAA1C162D6@microsoft.com... > I am trying to send a mailing list to another business. I was asked to send > it in excel and in comma format. How can I do this? File|SaveAs and look for "CSV (comma delimited)(*.csv)" Each worksheet needs to be saved ...

Excel public to web
I got some problem when I save the excel document to a web page. The document have some cells I want make it can be change by the users, and others should be locked. I use the protect under the tool, and I can make it in excel, but when it became to web style, all the document can not change because it shows like a read only document. What I can do for the document? Thanks for any answer! ...

Changing time format
I'm trying to chage the time format so they can be multiplied by a number Example: ( 6:30 - 2:45 - 1:30 - 2:15 ) * 3 = total amount due in dollars The four time are for different days Thanks for any ideas In order to multiply 'Time' by 'Currency' - you should ad a multiplication by 24 as well in the formula and format that(!) cell as 'Currency'. Micky "doodle302" wrote: > I'm trying to chage the time format so they can be multiplied by a number > > Example: ( 6:30 - 2:45 - 1:30 - 2:15 ) * 3 = total amount due in dollar...

SSRS 2008
I've creating a tablix based report using a dataset source that resembles an order, order item model. We have a tablix setup with the order item line as the detail group, and have a lot of information in the 'order header' that we are trying to display in the group header in the tablix. The report is splitting across pages in strange places and we think that this is because the header information is going over more than one page. It is trying to ensure that it fits a detail row on, but rather than spliting the table at the end (ie, when it runs out of page) it ...

Tough one
MVPs I have a few questions 1. How do we add more than 3 conditional formats ? Macros ?? Any add-in tools available to make life easier 2. Also, can we write macros to trigger some action, not just color cells based on conditions being met 3. How can we get a count and statistics of the cells that have been highlighted due to conditional formats ? I have a big text file with thousands of lines of records and when I populate that into excel, based on some conditions, the cells would light up. Now I would need to get some statistics of which type of formats are most prevalent etc 4. Ca...

How do I link excel to word so excel changes appear in word?
I have to write a word document in my submittals and some of what I d in excel I have to hand type into word. Is there a way I can typ something like "The total cost is $345,897" and have the number chang if my excel pricing changes so I don't have to keep changing the number in the word document? I know you can paste tables you make in excel into word, but what need is to beable to link a cell in excel to the middle of a sentenc in word -- caliskie ----------------------------------------------------------------------- caliskier's Profile: http://www.excelforum.com/mem...

What format should the XML text be in for SQL2k?
Hi, When passing serialized object's XML from ASP.NET to a SQL Server 2k sproc, what format should I have the text in? Here's my XML: <ArrayOfEmployee xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Employee empid="" firstname="Joe" lastname="Johnson" hiredate="01/01/2004" title="President" /> <Employee empid="" firstname="Bob" lastname="Smith" hiredate="02/01/2003" title="Vice President" /&g...

publisher 2000 cannot load old file format
I was given a Publisher file which I cannot load into Publisher 2000- I get the message "Publisher cannot open files from a different version." If I view the file in "binary" mode, I see the string "Publisher 3.0" which apparently dates it as being from the Office 95 era. A search of the Knowledge Base suggests this is a common problem with many (most?) versions of Publisher and the only way around it is to load the file into the version of Publisher that was used to create it. Of course, I don't have Publisher 3.0. Are there any applications tha...

Import text file in UTF-8 format to Excel 2000
Hello, When I open a text file(in UTF-8 format)under excel 2000, the chinese character cannot be displayed correctly. But this text file can be opened directly in word 2000. Can anyone help me? Thanks! Irene ...

Making actual projects with Excel 2007
I have read several books and so far have basic and general understanding of the mostly use features of Excel 2007. However, I am wondering if there are resources that can help me do actual projects. I learn quickly by doing practical stuff rather then reading a 1000 page book about features and why I should use a certain program. Were can I find tutorials on actual projects? Example: How to make a spread sheet for a grocery store. Any ideas or recommendation will be highly appreciated. Thanks everyone. Ai This may help http://office.microsoft.com/training/training...

Can you sort Excel data by font color?
I heard that you can sort Excel data by font or column colors. Is this true and how do you do it? Hi see: http://www.cpearson.com/excel/SortByColor.htm and http://www.xldynamic.com/source/xld.ColourCounter.html -- Regards Frank Kabel Frankfurt, Germany "Help Me Sort" <Help Me Sort@discussions.microsoft.com> schrieb im Newsbeitrag news:EF1E9176-5818-4185-9DD7-A74A613914E2@microsoft.com... >I heard that you can sort Excel data by font or column colors. Is this >true > and how do you do it? Only with VBA or helper columns: http://www.cpearson.com/excel/SortByC...

Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ...
Risk and Decision Analysis @Risk, Evolver, Decisiontools, Roy Kelly FloorTrader Tools 8.3, AmiBroker 5.0, MultiCharts 2.1, NinjaTrader 6, OwnData 2.6, VantagePoint 7.0.11.2, other ... please send e-mail to : ola 'AT' mail 'DOT' gr , ola3 'AT' mailbox 'DOT' gr , ( please substitute 'AT' with '@' , and 'DOT' with '.' ) , ola@mail.gr, ola3@mailbox.gr, ======================================= RISK ANALYSIS with @RISK --- @RISK 4.5.7 for Excel, Compatible with Excel 2007, ( Standard, Porfessional, Industrial ) @RISK 4.5.6 Internat...

"Excel volume locked"
I have backed up excel files using My Sonic DVD to a dvd disk. When attempt to open the backed up files from the the disk I receive misrosoft error message "can not save the file because the volume i locked". I am not attempting to save it just open it. All of the other files saved on this disk open fine, word, scan soft pictures... it just all excel files give me the same error message. Any thoughts or suggestions would be appreciated. Denn -- Message posted from http://www.ExcelForum.com When you open an Excel file it tries to write your name in it. That may be the proble...

Excel Data Connections to Access Queries: Limits?
I have an Access 2007 database with data connections to: + Sharepoint Lists + SMS SQL tables/views I've written Access queries that use tables from those data connections. I want to use Excel to connect to those queries in the Access database to run various reports (such as charts, lists, etc...) However, I'm having 2 problems: 1) In Excel, when I go to Data / Get External Data / From Access, I cannot see all of the queries listed in the Access Database. 2) The queries I do see only return a small subset of records. (I've tested the query within Access, and I should get 17...

Update from CRM 3.0 to new CRM 4.0
At the moment we are runnig CRM 3.0. Because of the new multi-language feature of CRM 4.0 we want tu upgrate/update to the new version. Does anybody has further information about the necessary steps? I suggest that you wait for the officially released version which will contain upgrade and installation guides. -- ----------------- Please note that the contents of my posts are my personal opinions and views and I am not a Microsoft staff member. ----------------- Feridun Kadir MCSE, MCT "Frank_Munich" wrote: > At the moment we are runnig CRM 3.0. Because of the new multi-...

MS CRM 3.0 on SQL Cluster 2000 Environment...
Has anyone installed MSCRM 3.0 on SQL 2000 Cluster and it is up and running with Reports, Outlook Client etc etc? CRM 3.0 supports only default instance on SQL Server. How about SQL Cluster? Is there any good document to implement CRM 3 in SQL Server 2000 cluster environment? I appreciate your help. Onkar http://www.microsoft.com/dynamics/crm/using/deploy/clusteringsqlservers.mspx "Onkar" <budc57@yahoo.com> wrote in message news:1160873095.913396.268610@b28g2000cwb.googlegroups.com... > Has anyone installed MSCRM 3.0 on SQL 2000 Cluster and it is up and > running wi...