# Search list, storing and returning values based on results

#### motoflyboy

##### New Member
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"
How do I post a sample spreadsheet to help explain this project?

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

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.

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)

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)

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

Hi
Can you explain the reason why E14 = 1?

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..

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?

Replies
6
Views
211
Replies
12
Views
484
Replies
5
Views
171
Replies
11
Views
3K
Replies
1
Views
253

1,221,383
Messages
6,159,539
Members
451,571
Latest member
Qwissy

### 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.

### Which adblocker are you using?

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

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