Search list, storing and returning values based on results

motoflyboy

New Member
Joined
Jul 28, 2005
Messages
9
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

New Member
Joined
Jul 28, 2005
Messages
9

ADVERTISEMENT

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

Consider this example:
Book1
ABCDE
1TimestampProductProductCodeRateStatus
223/09/200607:00SmallCarton100525251106
323/09/200607:02SmallCarton100525251125
423/09/200607:04SmallCarton100525251120
523/09/200607:06SmallCarton10052525950
623/09/200607:08SmallCarton100525250
723/09/200607:10SmallCarton100525250
823/09/200607:12SmallCarton100525250
923/09/200607:14SmallCarton100525250
1023/09/200607:16SmallCarton10052525864
1123/09/200607:18SmallCarton100525251118
1223/09/200607:20SmallCarton100525251115
1323/09/200607:22SmallCarton100525251120
1423/09/200607:24SmallCarton100525250
1523/09/200607:26SmallCarton100525260
1623/09/200607:28LargeCarton100546980
1723/09/200607:30LargeCarton100546980
1823/09/200607:32LargeCarton10054698750
1923/09/200607:34LargeCarton10054698762
2023/09/200607:36LargeCarton10054698780
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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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

New Member
Joined
Jul 28, 2005
Messages
9

ADVERTISEMENT

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
 

motoflyboy

New Member
Joined
Jul 28, 2005
Messages
9
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,917
Messages
5,545,024
Members
410,647
Latest member
bernardazar
Top