identify and extract cyle data

I am using this sheet as an example

http://homepage.ntlworld.com/george....%20example.xls

the data contains date/time info in column A and weight data in column B which cycles for when a tank is filled and subsequently emptied (indicated by values below 100)

I am looking for a script that will:-

a. identify these cycles using the column b data and seperate out or return the date/time values for cycle start and end
b. report the max value in column b for each cycle

The max value of b data will allow me to know which product was in the tank. I will then know the date/times for each time a particular product was made. I then want to extract the data from the rest of the columns for the specified dates

I am hoping this sounds lot more complicated than it actually is but I am completely lost when it comes to programming VBS and macros so I desperately need some help.

Thanks.

Could you perhaps add to your workbook a sample of your expected results?

Does the cycle end at the first value under 100 or the last value under 100 or something else altogether?

I can't update the file at the moment as I am at work and FTP uplolad is blocked

But as an example, I would hope to identify a cyle beginning on row 402 at 03/06/2009 19:21:54 and ending on row 1714 at 05/06/2009 15:05:54. This range has a max value in column B of 4504 so I would call this "product 1"

It would also identify a new cycle beginning on row 2210 at 06/06/2009 at 07:37:54 and ending on row 3937 at 08/06/2009 17:11:54. This range has a max value in column B so I call this "product 2"

Similarly the cycle starting at row 3944 and ending at row 4557 has a max value of 4016 in column B so is also "product 2"

It will maybe help if I explain what the data is. The info in column B is the weight if a bulk mixing vessel. At under approx 100 Kg it is nominally empty and then you can see from the data it is filled with various materials which are then mixed and the tank emptied. Two products are made in the tank and these have different formulas with different max weights so I can use this to interpret which product the data set belongs to.

After all the data in the sheet is analysed I want an output that says for example

Product start time end time

product 1 03/06/2009 19:21:54 05/06/2009 15:05:54
product 2 06/06/2009 07:37:54 08/06/2009 17:11:54
product 2 08/06/2009 17:25:54 09/06/2009 13:51:54

OK, so now if I have this info I want to extract/calculate the info from columns D to T for each range. I am probably looking for Max, mean and standard deviation calcs for each of these columns.(these columns are process measures from sensors on the tank/stirrer)

So the end goal is that then I can list the max, mean and sd of the process measures for all batches of Product 1 (and probably export these to a statistical package such as minitab for analysis)

To sum up what all this is trying to do. I have a datalogger that records all these process measures along a timeline but is useless to me for analysis as I can't see which batch is which and I have years of info to analyse which prevents me doing this manually.

I hope I have managed to explain this properly but any questions please post and thanks very much for haveing a look at it

I think I get most of that, but why is your third example also product 2 - I don't get that?

because the max weight of the tank is roughly the same, so we have made the same product/formulation

OK, but how are you defining "roughly"? Excel will need a rule. 5% difference, 10%...?

There a good few hundred kilos betwen them. I think I defined it as product 1 over 4300 Kg and prodcut 2 3900-4299 Kg when I was looking at it manually before

Does everything fall into product 1 or 2? If not, please post all the bands so we can produce code that works.

if it falls outside of those bands it will be something outside of normal production so it should be ignored

I have tried to do something manually to test the logic
http://homepage.ntlworld.com/george....rsion%201).xls

here I have
1. examined the column F cells to check the preceeding and following 5 cells and see if the averages are > or < 100 inorder to identify cycle starts and ends
2. I have had to add another column (probably because I don't know how to do it in a single calc) to take the first or last time the start or end batch indicator appears so I only have one for each cycle
3. I filer on these start an end cyle flags and then manually copy the time data data to the second sheet
4. I can then do calcs on these start and end times to find the max weight in the range and translate this to product
5. I can also start to do calculations on the other columns for the process data

As you can see this is rather cumbersome but I think the logic seems ok. There are probably much better ways to do it though.

I tried this format on a larger data set and I did get cycles with rougue data where the tank was half filled etc. I ignored this data based on it not achieveing the batch size expected

Also as an aside - I have no idea why this sheet with the same data set has jumped to nearly 6mb

Thanks

Originally Posted by StephenR

Does everything fall into product 1 or 2? If not, please post all the bands so we can produce code that works.

also, If you could make it clear in the code how to define these bands becasue there is more than one tank I will run the script on. The other tanks operate in the same way but will have different bands.

Thanks once again

I've made a start, but don't think is quite there. I don't get results exactly the same as yours - I suspect you have some latitude built into your assumptions which will need to be codified in some way and that could be quite difficult. Anyway, come back when you've had a go.

Please Login or Register  to view this content.

I ran it on the first example I linked to and got the following output

Product Start date/time End date/time
Product 1 03/06/09 06:01 03/06/09 19:07 $A$2 $A$395
Product 1 03/06/09 19:21 05/06/09 15:05 $A$402 $A$1714
Product 1 05/06/09 15:31 05/06/09 15:49 $A$1727 $A$1736
Product 1 05/06/09 17:01 05/06/09 17:09 $A$1772 $A$1776
Product 1 06/06/09 07:31 06/06/09 07:33 $A$2207 $A$2208
Product 1 06/06/09 07:37 06/06/09 09:47 $A$2210 $A$2275
Product 1 06/06/09 09:53 06/06/09 10:05 $A$2278 $A$2284
Product 1 06/06/09 10:15 08/06/09 17:11 $A$2289 $A$3937
Product 1 08/06/09 17:25 09/06/09 13:53 $A$3944 $A$4558
Product 1 09/06/09 14:11 $A$4567 $A$5042


1. I don't know if this is how it's meant to work but my interpretation of the output is that is is taking every instance where whe weight goes above 100 and then subsequently drops below 100 as a product. I liek this approach but I only want to see product 1 (cylcle reaches maximum >4300Kg) or product 2(cycle reaches maximum 3900 to 4299 Kg). We can ignore anything else. i could do this manually afterwards but if it coudl be built in that would be excellent

2, Everything is being tagged as product 1 but the only product 1 in the data is $A$402 to $A$1714

There are product 2's at $A$2289 to $A$3937 and $A$3944 to $A$4558. The last cycle identified is also liekly to be a product 2 but should be disregarded as there is no cycle end time in the data


I hope this helps.

Really appreciate you efforts on this.

Have corrected a mistake, and switched product numbers. What happens if the max is less than 3900? The code could be added to remove any rows where the max is less than this.

Please Login or Register  to view this content.

This is the output I got (have added max to the last column):

PHP Code: 

Product 1    03/06/2009 06:01    03/06/2009 19:07    $A$2    $A$395    4530
Product 1    03/06/2009 19:21    05/06/2009 15:05    $A$402    $A$1714    4504
Product 2    05/06/2009 15:31    05/06/2009 15:49    $A$1727    $A$1736    118
Product 2    05/06/2009 17:01    05/06/2009 17:09    $A$1772    $A$1776    104
Product 2    06/06/2009 07:31    06/06/2009 07:33    $A$2207    $A$2208    101
Product 2    06/06/2009 07:37    06/06/2009 09:47    $A$2210    $A$2275    107
Product 2    06/06/2009 09:53    06/06/2009 10:05    $A$2278    $A$2284    100
Product 2    06/06/2009 10:15    08/06/2009 17:11    $A$2289    $A$3937    4010
Product 2    08/06/2009 17:25    09/06/2009 13:53    $A$3944    $A$4558    4016 


I get the same output as you

Yes, anything with a cycle peak below 3900 should be ignored

Make it clear where these "categorizing" values go in the code so I can change them for other products and data sets

It's looking good so far though, thanks

The category is defined as nThreshold in the third line.

Please Login or Register  to view this content.

looking good now.

My only issue is that it is picking up a cycle starting at A2 which doesn't have a start point <100.

Not sure how easy that is to fix. it would be a hardship for me to manually look at the first one identified to see where it starts.

The next stage I'd liek to accomplish is that now that the products are identified. I woudl like to dump all the data for product 1 to one sheet, say "product 1 data" and all product 2 data to another sheet.

definately nearly there now - can't tell you how many hours this will save me!

that's not quite what I meant

I still want to leave the full list in "sheet 2"

And in "product data 1" I want the contents copied from the entire rows in "sheet 1" that relate to product 1

and "product data 2" contains the contents copied from the entire rows in "sheet 1" that relate to product 2

Thanks

good morning, trust you had a good weekend

do you think the modifications I described to the script are possible?

Thanks (sorry to be a nuisance)

Try this:

Please Login or Register  to view this content.

looks perfect - thanks for all your efforts

I will go and try and work it on some large data sets now

I have managed to take the script and run with it, learning a little bit and making a few variants which has been great, thanks.

There is only one thing I don't understand I think (which is probably obvious but remember I am a noob)

what does the (2) do at the end of this line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

is equivalent to

Please Login or Register  to view this content.

and finds the next cell down from the last filled non-empty cell. You can reference any cell in this way, for example

Please Login or Register  to view this content.

is equivalent to

Please Login or Register  to view this content.

.

Does that help?

Yes i think i get it

End(xlUp) woudl give me the last filled cell so by putting the (2) is drops down to the next cell

Thanks