Search list, storing and returning values based on results

[motoflyboy]

Hi
I sort of have the some small peices of this code worked out but (as a relative newb) nothing that works.I am out of my depth on this one!
Can someone point me in the right direction?
I have a table of data which i need to search through (approx 20000 rows).
Column C contains a 'ProductCode' and column D contains a 'Rate'
I need to provide a value in column E which represents 'status'
Code needs to:
Look down Column D to find when the value of 'Rate' drops below 100.
If Column D value is < 100, look down column C to check if 'ProductCode' changes before 'Rate" becomes >100.
When 'ProductCode' does change, I need the value of E(Status) to change for each row where D(Rate) was <100.

I hope i have explained this OK.
One of the major blocks for me is how to get the code to remember which was the first row where columnD was < than 100, then going BACK UP the table to find that row and change the values of columnE IF it does find that column C changes before column D becomes>100.

 

[motoflyboy]

How do I post a sample spreadsheet to help explain this project?

 

[pgc01]

Hi motoflyboy
Welcome to the board

I have 2 questions

When 'ProductCode' does change, I need the value of E(Status) to change for each row where D(Rate) was <100

1 - Change to what? What is the value you want written on Status?

2 - And after you find such a case? Suppose D1:D10 with rates < 100 and C1:C2=Code1, C3:C5=Code2, C6:C10=Code3. What do you want to happen?

Kind regards
PGC

 

[pgc01]

P. S. At the end of this page you have a link to Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

 

[motoflyboy]

Trying to determine when a machine is stopped in the middle of a run or stopped for a machine change.....

Consider this example:

Book1
	A	B	C	D	E
1	Timestamp	Product	ProductCode	Rate	Status
2	23/09/200607:00	SmallCarton	10052525	1106
3	23/09/200607:02	SmallCarton	10052525	1125
4	23/09/200607:04	SmallCarton	10052525	1120
5	23/09/200607:06	SmallCarton	10052525	950
6	23/09/200607:08	SmallCarton	10052525	0
7	23/09/200607:10	SmallCarton	10052525	0
8	23/09/200607:12	SmallCarton	10052525	0
9	23/09/200607:14	SmallCarton	10052525	0
10	23/09/200607:16	SmallCarton	10052525	864
11	23/09/200607:18	SmallCarton	10052525	1118
12	23/09/200607:20	SmallCarton	10052525	1115
13	23/09/200607:22	SmallCarton	10052525	1120
14	23/09/200607:24	SmallCarton	10052525	0
15	23/09/200607:26	SmallCarton	10052526	0
16	23/09/200607:28	LargeCarton	10054698	0
17	23/09/200607:30	LargeCarton	10054698	0
18	23/09/200607:32	LargeCarton	10054698	750
19	23/09/200607:34	LargeCarton	10054698	762
20	23/09/200607:36	LargeCarton	10054698	780
Sheet1

1.Status needs to simple be a number ie:
Status = "1" If D(Rate) drops below 100 AND C(Code) DOES NOT change - prior to rate increasing to > 100.
Status = "2" If D(Rate) is > 100
Status = "3" If D(Rate) is drops below 100 AND C(Code) changes before rate increasing to > 100.

So for the example above:
E2:E5 = 2 (Rate >100)
E6:E9 = 3 (Rate<100 AND NO change in C)
E10:E13 = 2 (Rate > 100)
E14:E17 = 1 (Rate< 100 AND a change in C)
E18:E20 = 2 (Rate> 100)

 

[agihcam]

Hi -
just to clarify, are these result you have posted correct? or I didn't understand it correctly?

E6:E9 = 3 (Rate <100 AND NO change in C)
E14:E17 = 1 (Rate < 100 AND a change in C)

 

[motoflyboy]

What you have quoted is correct for the sample data I posted....

These are the results I need whatever code we come up with to generate.

IE: populating column E with 1,2 or 3 dependant on the values in D and C

 

[jindon]

Hi
Can you explain the reason why E14 = 1?

 

[motoflyboy]

Yes:
We are looking to determine the operational status of a machine.
So operationally for a product CHANGE, the machine rate will drop down to < 100 then the operator will change the program (code) to start the next product. Then the rate will increase as the machine starts up on the next product.
So we need to look at the rate to see if it has dropped to below 100 (idle speed) Then IF THE PRODUCT CODE CHANGES, assign a status of "1" (changeover) for ALL of that block of time that the machine was below 100 ie: from when the rat drops to when the rate increases.

Not sure if there is an easier way to explain this. Herein lies the struggle i'm having with the structure of the code..

 

[jindon]

Hummm...

When you look at D5:D6 and C5:C6, I know E6 = 1 as no change in C5:C6.

So should the same be applied in E14 as no change in C13:C14?