Automating Selection

[crusher32]

I have a question for all those with more Excel experience than I. We perform thermal cycling here at work and include event detection. The thermal cycles are performed in 100 cycle sessions. The event detection tells us when an electrical part has failed. When a part fails, the programs returns the thermal cycle number and the temperature as which the failure occurred. To avoid false positives we do not remove/short the channel until this process has occurred during consecutive thermal cycle sessions. The spreadsheet I have set up shows the data in a horizontal form (cycle #, temperature). Presently, when a first event/temp is enter into the spreadsheet I create a formula in Col B in the form (for row 6) if(J6+L6=J6,"",J6). This keeps B6 blank until there is an event entered in Col L6. As you can see from row 4, the data is enter in B4 when there has been event in two consecutive sessions. The entering of the formula is both time consuming and are a point of possible error. I am looking for a formula/function that will look for the event/temp data across several thousand cycles (Columns A to infinity), find the first event/temp, verify there is a subsequent one in the next thermal cycle session (eg. Col N and P) and then take the data from the first set of event/temp and record it in Col B. It should do the same for the temperature info in Col C. I hope that I have explained my issue sufficiently and hope that someone can offer a solution.


[TABLE="width: 771"]
<colgroup><col><col span="3"><col span="14"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ch #
[/TD]
[TD]Failure Cycle[/TD]
[TD]Failure Temp[/TD]
[TD="colspan: 2"]1[/TD]
[TD="colspan: 2"]101[/TD]
[TD="colspan: 2"]201[/TD]
[TD="colspan: 2"]226[/TD]
[TD="colspan: 2"]301[/TD]
[TD="colspan: 2"]401[/TD]
[TD="colspan: 2"]501[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="colspan: 2"]100[/TD]
[TD="colspan: 2"]200[/TD]
[TD="colspan: 2"]225[/TD]
[TD="colspan: 2"]300[/TD]
[TD="colspan: 2"]400[/TD]
[TD="colspan: 2"]500[/TD]
[TD="colspan: 2"]600[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[TD]Cycle[/TD]
[TD]Temp[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]425[/TD]
[TD]127[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]425[/TD]
[TD]127[/TD]
[TD]540[/TD]
[TD]-26[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]263[/TD]
[TD]78[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Thanks

Jerry
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[raymhuber]

I have a quick question.

If the formula in B6 is

=if(J6+L6=J6,"",J6)

how does it have a value? Both L and J in row 6 are blanks, 425 appears to be the value of N6 which isn't in your formula.

-Ray

 

[crusher32]

Ray
You got me.

The formula in cell B4 should be =if (N4+P4=N4,"",N4). If there is a value in both N4 & P4 the value of N4 would show in cell B4

 

[raymhuber]

My first thought would be to use the MATCH function in combination with an IF function to find out if there is another value 2 columns from the successful match function. This would only work for the first column that had a value though.

Would a VBA function be ok for this task? I would definitely know how to create that.

-Ray

 

[crusher32]

Just Googled VBA function. If you think this would work for many row and columns of data. The parts can fail at any time.

-Jerry