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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

pgc01

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

Forum statistics

Threads
1,140,938
Messages
5,703,271
Members
421,289
Latest member
fbohlandt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top