VBA Macro on Excel
I will describe what the macro will do through the Excel [url removed, login to view] provided in the Zip file.
On the column C, we have TIME data. Here is how to ‘read’ those numbers. The first one (092508) means 9h25 and 8 seconds. Also, « 145639 » would mean 14h56 and 39 seconds.
The Macro will ‘create’ 12 new columns, from AZ to BK with the folling order and titles (to be written in the first cell of each column): CS cumul, CT cumul, SV5 cumul, SV4 cumul, SV3 cumul, SV2 cumul, SV1 cumul, BV1 cumul, BV2 cumul, BV3 cumul, BV4 cumul.
They are already created on the provided Excel file to show how the result should look like. The macro will work without needing the data I manually created in columns AZ&#61664;BK and will act like there is a blank there.
The macro will do the same kind of calculus for the twelve columns. I will thus explain for the first one.
In a given cell of the ‘CS CUMUL’ created column (in AZ), the macro will add up the number from the same line and in the column called ‘CS’ (in column E), plus the number from the cell above itself. You can observe such formulas in all red cells from column AZ to BK.
BUT, when the TIME cell indicates a new 5 minutes period, the formula on the created column won’t include the above cell number. Such formulas are in green.
Indeed, in the C (‘TIME’) column, the line 2 is a new period of 5 minutes, so is the line 3 (9h25 8s and 9h30 8s). But, from the line 3 (9h30 8s) to the line 46 (9h34 58s), we are within a same 5 minutes interval (9h25 0s --> 9h29 59s) so that the formulas in the AZ&#61664;BK columns must include adding up the above cell.
Note : some TIME DATA patterns may be like : 10h28 & 6s, 10h31 & 8s , 10h33 & 40s, 10h37 & 2s. Please, make sure that the macro detects 3 different 5 minutes intervals in such a case.
The macro does not need to include colors pattern as used in the provided Excel file
29 freelancers are bidding on average $310 for this job
Hi, I can't find your attachment, but based on your description of the task, my skillset is perfect for this task. Please see message board. francis,adVBAncedCoder UNLEASH your MS OFFICE
It looks like that I can handle this task in two days weekend. I have 2 years experience of implementing financial calculus tasks in Excel VBA. So your problem will be fairly easy for me.
I have worked with Excel macros a lot, specially even real time applications containing up to 100,000 data rows. I think i can work this out quite well. Cheers....