Finding the last value less than 24 hours

[alex_a]

Hi,

I have a list of customer names in column A of sheet 1,their product testing results in columns O, R, U, and X, and the time tests completed in column AK. I need a way to populate sheet 2, column A through F (B, C, D, E, for test results option) and F for the time test conducted with the customer name, test dates and test result( last test date less than or equal 24 hours). In column Ak of sheet ,we may have different hours of tests from1 hour to 40 hours).I need the very last value close to time 24 and its corresponding test results (pass/fail in columns O,R,U, and X).
Thanks for your help.

 

[sijpie]

Re: Finding the last valu eless than 24 hours

Hi Alex,

can you redefine what you need. Your description is difficult to understand as you mention what you need in severeal columns at once.

Sh1.Col A Customer Name goes to Sht2.Col B
Sh1.Col U Result multiplied by 3 goes to Sht2.Col F
etc
What do you mean with very last value close to time 24 and its corresponding test result?

Also small example will certainly help (forget about the columns with which you have no problem) such as:

Input sheet

	A	B	C
1	Client	Result	TestTime (hr)
2	Client 1	OK	34
3	Client 2	OK	18
4	Client3	Fail	63

<tbody>
</tbody>

Output sheet

	A	B	C
1	Client	xxx	yyy
2
3
4

<tbody>
</tbody>

 

[alex_a]

Re: Finding the last valu eless than 24 hours

Hi Alex,

can you redefine what you need. Your description is difficult to understand as you mention what you need in severeal columns at once.

Sh1.Col A Customer Name goes to Sht2.Col B
Sh1.Col U Result multiplied by 3 goes to Sht2.Col F
etc
What do you mean with very last value close to time 24 and its corresponding test result?

Also small example will certainly help (forget about the columns with which you have no problem) such as:

Input sheet

	A	B	C
1	Client	Result	TestTime (hr)
2	Client 1	OK	34
3	Client 2	OK	18
4	Client3	Fail	63

<tbody>
</tbody>

Output sheet

	A	B	C
1	Client	xxx	yyy
2
3
4

<tbody>
</tbody>

-------------------------------------------------------------------------------------------

 

[sijpie]

Re: Finding the last valu eless than 24 hours

something went wrong! the image did not get through

 

[alex_a]

Re: Finding the last valu eless than 24 hours

Input sheet:

Outputs Sheet:

 

[sijpie]

Re: Finding the last valu eless than 24 hours

This macro will do the trick. Read the comments and check & modify where these start with <<<<
Make sure your sheet names are exactly the same as the names in the sheet tabs (in your example sheet the Main ssheet had a trailing space)

Option Explicit

Sub GetLastTime()
'////////////////////////////////////////////
'//  Transfers last test results of each   //
'//  test for each client from input sheet //
'//  to output sheet Populated Data.       //
'//  Assumes that the data in input sheet  //
'//  are in order of client and that the   //
'//  test are also in order (last one last)//
'////////////////////////////////////////////
    Dim vInp As Variant, vOutp As Variant
    Dim lRi As Long, lRo As Long, lRch As Long, lC As Long, UB1 As Long, UB2 As Long, lO
    Dim wsInp As Worksheet, wsOutp As Worksheet
    Dim clUniq As New Collection
    
    Set wsInp = Sheets("Main")                      '<<<< Name of input sheet
    Set wsOutp = Sheets("Populated Data")           '<<<< Name of Output sheet
    
    ' Read input data into array for fast processing
    vInp = wsInp.Range("A1").CurrentRegion.Value    '<<<< first cell of input range
    UB1 = UBound(vInp, 1)    'number of rows
    UB2 = UBound(vInp, 2)    'number of columns
    
    ' count the unique clients. Do this by adding into collection
    On Error Resume Next ' stop error when adding key twice
    For lRi = 1 To UB1
        clUniq.Add vInp(lRi, 1), vInp(lRi, 1)
    Next lRi
    On Error GoTo 0     ' reset error behaviour
    ' Set up the output array
    ReDim vOutp(1 To clUniq.Count + 1, 1 To UB2)
    
    
    ' now copy from bottom up (from array vInp to array vOutp)
    lRo = UBound(vOutp, 1)
    For lRi = UB1 To 1 Step -1
        If vInp(lRi, 1) <> vOutp(lRo, 1) Then   ' Client number changed
            'copy to next row up
            lRo = lRo - 1
            For lC = 1 To UB2
                vOutp(lRo, lC) = vInp(lRi, lC)
            Next lC
        End If
    Next lRi
    
    'then dump the output array to Populated Data sheet
    wsOutp.Range("A1").Resize(UBound(vOutp, 1), UB2).Value = vOutp
    
End Sub

 

[Legacy 421546]

Re: Finding the last valu eless than 24 hours

One important thing to know about dates in Excel is that it's internally represented as a decimal number. The integer part is number of days from year 1900. The fractional part is number of seconds since the start of this day.
With this in mind it's easy to accomplish most tasks regarding dates.

Tip: If you change the cell format to 'General' you'll see the underlying date serial number

 

[alex_a]

Re: Finding the last valu eless than 24 hours

One important thing to know about dates in Excel is that it's internally represented as a decimal number. The integer part is number of days from year 1900. The fractional part is number of seconds since the start of this day.
With this in mind it's easy to accomplish most tasks regarding dates.

Tip: If you change the cell format to 'General' you'll see the underlying date serial number

But this is not what Iwas asking.

 

[alex_a]

Hi,

Thans for the effort but I don't see any logic related to "24 hours". It just picks up the largest value.

 

[Yongle]

but I don't see any logic related to "24 hours". It just picks up the largest value.

The LOGIC is what happens if you go past midnight

6pm = 0.75
noon = 0.5

6pm TODAY (= 0.75 ) is obviously earler than noon TOMORROW (= 0.5 ) BUT 0.75 is bigger than 0.5

Without incorporating the DATE value the calculation is unreliable