Selection of samples using VBA based on Time

[ravikiran]

Dear Gurus,


I have a complex issue to be solved (complex enough for me at this moment). Please help me achieve this VBA code.


Please find the link to the sample file here: https://rapidshare.com/files/2211042122/Sample.xlsx


I have a list of records with Date and Time, from this we need to scan through the time and pick those samples at designated timings. E.g: There are 50 samples with date and time as below:


I have to pick the samples at 00:30, 1:30, 2:30 and 3:30 (I need to hardcode them into variables, as the intervals might change to 2 hours instead of 1hr). So in case of the last two records at "3:26 & 3:37", I'll pick the "3:26" sample and mark it with a "1" in the cell adjacent to it. Similarly between records with "2:23 & 2:34", I need to pick "2:23" (Ofcourse I got the data sorted in ascending order of time).


One more small additional requirement is whenever there is a change in the "Lot Number", a sample got to be SELECTED, whether they fall in the time frame or not, in addition to the other samples.


I am unable to achieve this logic. Your help will be greatly appreciated.


Thanks in advance.


Regards,

RK.

 

[Luke M]

I'm afraid I can't access uploaded workbooks, but perhaps I can make a good guess at the formula.


Assuming this layout:

[pre]

Col A        Col B
Start Time	0:30
Time Interval	1:00

TimeValue	Lot #
0:12	         1
0:26	         1
1:15	         1
1:26	         1
1:40	         2
2:25	         1
2:26	         1
2:31	         1
Array formula in C5 is:

=1*OR(A5=LOOKUP($B$1+$B$2*(ROW($1:$100)-1),$A$5:$A$100),AND(B5<>B4,B5<>B6))


Remember to confirm the array using Ctrl+Shift+Enter, not just Enter.

output should be:

TimeValue	Lot #	Formula
0:12	          1	  0
0:26	          1	  1
1:15	          1	  0
1:26	          1	  1
1:40	          2	  1
2:25	          1	  0
2:26	          1	  1
2:31	          1	  1

[/pre]

 

[ravikiran]

Thanks Luke. The formula is awesome.


But I had couple of problems. One is that there will be different "Lot Numbers", which the formula is not tracking. But I can use a Helper column and achieve this.


Second issue is that I have the time in A Date-Time format. I am unable to modify the formula to suit this. I have pasted the data for your reference. Can you also please explain the formula for my understanding?

[pre]

Record Time	        Lot Number  SelectionsMade
26/06/2012 00:04	    222
26/06/2012 00:16	    222
26/06/2012 00:25	    222     YES
26/06/2012 00:36	    222
26/06/2012 00:46	    222
26/06/2012 00:57	    333	    YES
26/06/2012 01:07	    333
26/06/2012 01:17	    333	    YES
26/06/2012 01:39	    333
26/06/2012 01:49	    333
26/06/2012 02:07	    444	    YES
26/06/2012 02:11	    444
26/06/2012 02:23	    444	    YES
26/06/2012 02:34	    444
26/06/2012 02:44	    444
26/06/2012 02:54	    444
26/06/2012 03:05	    444
26/06/2012 03:15	    444
26/06/2012 03:26	    444	    YES
26/06/2012 03:37	    444

[/pre]
Is there a way to achieve this using VBA? That would suit my purpose better.


Once again thanks for your help Luke.


RK.

 

[Luke M]

How's this for a macro?

[pre]

Sub TimeCheck()
Dim LastRow As Integer
Dim TimeBit1 As Double
Dim TimeBit2 As Double

Dim StartValue As Double

'Defined by you, as number of minutes
StartValue = 30

LastRow = Range("A65536").End(xlUp).Row

Application.ScreenUpdating = False

For i = 2 To LastRow - 1
TimeBit1 = Right(Format(Cells(i, "A"), "hh:mm"), 2)
TimeBit2 = Right(Format(Cells(i + 1, "A"), "hh:mm"), 2)

'Check for lot number change
If Cells(i, "B") <> Cells(i - 1, "B") And i <> 2 Then
Cells(i, "C") = "YES"
ElseIf TimeBit1 <= StartValue And TimeBit2 > StartValue Then
Cells(i, "C") = "YES"
End If
Next

Application.ScreenUpdating = True

End Sub

[/pre]
The formula was (attempting to) work by doing a lookup of all the half hours marks (0:30, 1:30, 2:30, etc) against what I thought was a column of times. If the value in col A matched any one of the lookups (indicating it was the closest value to a 30 minute mark w/o going over) then mark the cell true (aka, 1). The back half of the OR function was just trying to detect the different lot numbers. Either way, I think this macro will work better for you.

 

[ravikiran]

Awesome Luke. Worked perfect.


I need the timings to be customized and consider the date as well, which I did some more changes on to the code. Otherwise, the logic is perfect. Stupid of me not to think of that


Thanks again,


Regards,

RK

 

[Luke M]

Glad to help!