Pull through data from table based on oldest date

[clarkbatfan]

Hi all,

I have a three column table, one column with a name, another with a date/time timestamp, and a third is whether that row is completed.

I basically need to pull through a name to another sheet, but it has to be the oldest uncompleted. I just can't suss how to do it!

Someone has probably got an easy way, so thanks in advance if so!

 

[Joe4]

You can see how to do it based on one condition, as shown here: https://www.extendoffice.com/documents/excel/3333-excel-find-earliest-date-based-on-criteria.html

However, I can not come up with a good way to do multiple conditions with an Excel formula. I could come up with a way to do it in Access pretty easily, and might be able to come up with a VBA solution (might not be the most efficient, but it would probably work!). I don't know if VBA is an acceptable solution for you. If it is, let me know, and I will see what I can come up with.

 

[sandy666]

it can be done with PowerQuery aka Get&Transform I think

 

[Joe4]

it can be done with PowerQuery aka Get&Transform I think

I would imgaine so, as I see a lot of similiarities with PowerQuery and Access (or SQL)!

 

[sandy666]

excel doesn't contain access but power query

 

[Joe4]

excel doesn't contain access but power query

I know. This was just a follow-up to this comment I made earlier:

I could come up with a way to do it in Access pretty easily

 

[sandy666]

sure

 

[clarkbatfan]

You can see how to do it based on one condition, as shown here: https://www.extendoffice.com/documents/excel/3333-excel-find-earliest-date-based-on-criteria.html

However, I can not come up with a good way to do multiple conditions with an Excel formula. I could come up with a way to do it in Access pretty easily, and might be able to come up with a VBA solution (might not be the most efficient, but it would probably work!). I don't know if VBA is an acceptable solution for you. If it is, let me know, and I will see what I can come up with.

Not opposed to VBA at all! I did some searching to try and see a few ways to do it through VBA, but nothing i did worked. Any help you can give would be great!

 

[Joe4]

What exactly are the possible options for the third column, that indicates whether or not it is complete?

 

[Joe4]

OK, assuming the complete column has "Yes" or "No" in it, here is a User Defined Function that should do what you want.
Just create a new blank module in your workbook in the VB Editor, and add copy/paste the code there, and then you can use it like any other Excel function.

Function GetOldest(rng As Range, nme As String) As Variant
'   Return the oldest date for an incomplete record for a person
'
'   Parameters:
'       rng: enter the first column in the range, and assume the other columns immediately follow
'       nme: enter the name of the person you are looking up
'
'   Assume:
'       1st column is name
'       2nd column is date/time stamp
'       3rd column is complete flag (Yes/No)

    Application.Volatile

    Dim minDate As Date
    Dim cell As Range
    
    For Each cell In rng
'       See if name matches
        If nme = cell.Value Then
'           See if record is incomplete
            If cell.Offset(0, 2) = "No" Then
'               Check date
                If minDate = 0 Then
                    minDate = cell.Offset(0, 1)
                Else
                    If cell.Offset(0, 1) < minDate Then
                        minDate = cell.Offset(0, 1)
                    End If
                End If
            End If
        End If
    Next cell
    
    If minDate > 0 Then
        GetOldest = minDate
    Else
        GetOldest = "N/A"
    End If

End Function

So, let's say that your data is in range A2:C10, and the name that you want to look up is in cell J1.
Then, you would just use this formula:
=GetOldest(A$2:A$10,J1)