Finding the last value less than 24 hours

A

alex_a

Board Regular
Joined
Feb 27, 2012
Messages
51
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.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Client
[/TD]
[TD]Result
[/TD]
[TD]TestTime (hr)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Client 1
[/TD]
[TD]OK
[/TD]
[TD]34
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Client 2
[/TD]
[TD]OK
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Client3
[/TD]
[TD]Fail
[/TD]
[TD]63
[/TD]
[/TR]
</tbody>[/TABLE]


Output sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Client
[/TD]
[TD]xxx
[/TD]
[TD]yyy
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Finding the last valu eless than 24 hours

sijpie said:
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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Client
[/TD]
[TD]Result
[/TD]
[TD]TestTime (hr)
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Client 1
[/TD]
[TD]OK
[/TD]
[TD]34
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Client 2
[/TD]
[TD]OK
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Client3
[/TD]
[TD]Fail
[/TD]
[TD]63
[/TD]
[/TR]
</tbody>[/TABLE]


Output sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Client
[/TD]
[TD]xxx
[/TD]
[TD]yyy
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
-------------------------------------------------------------------------------------------
 
Last edited by a moderator:
Upvote 0
Re: Finding the last valu eless than 24 hours

something went wrong! the image did not get through
 
Upvote 0
Re: Finding the last valu eless than 24 hours

Input sheet:
6PBJIQQQgghNwL4f0CrZ2qrz9 gAAAAAElFTkSuQmCC



Outputs Sheet:
4jgAAAAAG67n98uijZWy3dVQAAAABJRU5ErkJggg==
 
Upvote 0
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)
Code: 
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
 
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
Re: Finding the last valu eless than 24 hours

johnny_j said:
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
Click to expand...

But this is not what Iwas asking.
 
Upvote 0
Hi,

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

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
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
count how many times text string appears in column
Replies
2
Views
225
ajm
A
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S
S
  • Solved
Rotating Data from Horizontal to Vertical using VBA Code
Replies
3
Views
390
kevin9999
K
H
  • Solved
Create list of last(family) names when parents have children in more than 1 class
Replies
7
Views
172
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top