Need a VERY dynamic macro for data analysis

Hello All,

I have already tried to do this and ended up with an enormous macro that takes an eternity to run. Thus I hand the project over to you folks to try and help me out.

TestData.xlsm Here is an example of two months worth of data that I have.

Now here are some complications:

• Each serial number (col B) goes with a unique test, however if that serial number appears on two different days (col A) they should be considered separate tests
• This dataset can be of any length and thus any date range (col A)
• I'd like to do alot of analysis on this data which can end up making the macro rather complicated

Now essentially when Oil Temp In (col AD) or Oil Temp Out (col AE) goes under 125 that is considered a failure. I need to see if failures correlate to the following variables:

• Rotor Speed (col D) (ranges from 0-30000, should be broken up into ranges)
• Number of unique tests on that day (derived from col A)
• Length of the test that value is in (derived from col A)
• Ambient Temperature (col X)
• Month that test is in (derived from col A)

This could be done in a number of ways. The way I was doing it before was by finding the percent of failures in each category (i.e. number of failures when Rotor Speed is between 0-1000 / total instances when Rotor Speed is between 0-1000 * 100).

If anyone has any ideas on how I should go about any of this please help me out. I am not ignorant at coding, I am just not fluent enough in VBA to write codes with operating speeds in mind. I will monitor this thread constantly in case anyone has any questions.

Hopefully I will see a good turnout of help here.
Thanks!

Here are some of the data analysis...

I could not see how to calculate the duration.

The only set I would code into VBA is the first one Date-TestID one since it is bound to change...

rcm: That stuff looks great. However I don't see any macros that accomplished it. I need a way that I can replicate your process each time the data on Sheet 1 changes. As for the duration it would simply be the final time stamp - the first time stamp of any unique test. Keep in mind however that if a unique test extends into another day then it should be considered separate.

arlu: I'm sure you are more creative then I. A few things I think would be helpful would be a bar graph of failures vs. month, histograms of ambient temp and rotor speed ranges vs. failures, etc. Please feel free to apply your own creative genius as two minds are better than one.

For anyone: A method of finding the number of tests done on a unique day and comparing that to the number of failures would also be helpful.

I really like rcm's format so try and use that as a starting point.

Thanks everyone!

Ferloft:

Since you mention speed, the "coditional counting" is done for the entire sheet so any changes would be reflected automatically. I'll write the code to update just the test id results since that's about to change and also the macro will handle the duration. It a test goes into the next day, it would be reported in the day that the final time stamp belongs...

OK just press "button" and will calculate any date related stat.

I must be really blind but which column contains the end time stamp?

rcm: The Month/Year column is also going to change with the list of data so that will need to be handled via macro as well. As for the duration, if the test goes into the next day it's going to have an incorrect duration as it will count the time overnight. This is why it needs to be considered as two different tests (one on each day). Also, what are your thoughts on comparing the number of tests done in a day to the number of failures? Is this possible?

Also rcm, if you can...please try to avoid doing any data processing on the data tab. If you can, have the macro perform data processing on Sheet 3 or somewhere other than Sheet 1.

arlu: If you take a look at the workbook rcm provided that is exactly what I am looking for in terms of output. If you can think of some awesome way to display those outputs be my guest! I'd much appreciate it!

If I can think of anything else I'm looking for I'll be sure to post it here. You guys are awesome!

rcm: The end time stamp would be the final time stamp associated with a test.

Consider the following:
pic.JPG

The start time would be 12/15/2011 11:33 and the end time would be 12/15/2011 11:48, resulting in a duration of 15 minutes (note column A also includes seconds, which would be included in calculations)

OK no calcutations done in data tab or in results tab . The macro handles all calculations.

I'm very impressed with your work rcm, hopefully it will still run fast when there are 30000 records as opposed to only 1100. Hopefully you saw my post about calculating duration and can take a stab at that part. I really appreciate your help!

I got the time elapsed cicle done (using sheet3 as a calc tab!!!

post #10, maybe there eare better ways to do it. if you get one of the forum moderators (VBA masters) interested .What I did is to turn off calculations and screenupdating while it is crunching away the stats...

I ran the macro with a 30 times cycle: 30 x 1000 = 30000 and it took less than 3 minutes!!!!er

Running on 64-bits Office 2010 on a computer with an I7 pro processor under windows 8

Hey rcm, I know this might be asking a bit much but would you mind putting a few comments in your code? I'm having a little bit of trouble keeping up with all your "r#" variables and a few comments would definitely help me follow all your logic. I only ask this so that if I need to modify things it will be a bit easier for me.

Also, I think the duration section should be in ranges (i.e. tests 0-1 min, 1min1sec-2min, etc.)

Thanks!

Here is a commented macro + the minute ranges

The minute ranges look correct, but when I go to run your macro it just overwrites that column and reverts back to seconds and not minutes or ranges

I'm sorry for the inconvenience...I did not take out the section in seconds

but it is corrected now..

Columns f:h are the minutes and seconds for each run. it is needed for the calculation.

In sheet3 I did a sum check for all the reports

Sheet4 is not used...

Another category to add would be the number of unique items in a day vs. the number of failures. This should be fairly easy to add since you already made each test ID have a unique day included in it

I added the results for the daily # of test that were conducted and the number of test that had failed runs

Good Morning rcm,

Here is your code with my added updates. TestData Final.xlsm Let me know what you think and if anything else should be added.

I am now going to work on taking your data and making it presentable (graphs and such), so that will be a whole new project.

Thank you for all your help. If I run into anything else I would like to add I will make sure to come post it here.