Como encontrar a última data/hora na coluna usando macro

Question

Tenho dados gerados a partir do sistema que se parecem com a tabela abaixo. Na última coluna preciso exibir o usuário que foi o último a atualizar o sistema de cada departamento.

Update Time     User    Department  Last update  
-------------------------------------------------------
1/19/12 7:26    John    A
1/19/12 6:26    Yen     A
1/18/12 9:47    Jefta   B
1/18/12 9:47    Jefta   B
1/18/12 9:47    John    A

Answer 1

SE eu entendi o que você está perguntando, e isso está no Excel, você poderia usar uma "fórmula de matriz".

Por exemplo, se o seu conjunto de dados estivesse em uma planilha no intervalo A1:C5, você poderia usar isto na coluna "D":

{=INDIRECT("B" & MATCH(MAX(IF(C$1:C$5=C1,(A$1:A$5),)),A$1:A$5,0))}

Esta fórmula criará dinamicamente a referência de célula que apontará para o nome de usuário necessário, retornando o número da linha onde a data máxima foi encontrada, mas apenas para as linhas em que o valor do departamento corresponde ao valor dessa linha na coluna "C" (a coluna do departamento) e anexando-o ao caractere de texto "B" (a coluna que contém os nomes de usuário), criando assim a referência de célula "letra + número" inteira.

Observe que isso precisará ser uma função de array, o que significa que você deve manter pressionado Ctrl+ Shift+ Enterao sair da célula após digitar a fórmula, em vez de apenas inserir como faria normalmente. Quando você fizer isso corretamente, a fórmula será colocada entre colchetes, conforme mostrado acima. Se não houver chaves envolvendo a fórmula, ela não será configurada como uma fórmula de matriz e não funcionará corretamente.

Pode haver uma maneira mais simples ou elegante de fazer isso, mas se você precisar apenas de uma solução rápida e suja, funcionará.

Se você precisar de mais explicações, posso fornecer mais detalhes.

Answer 2

Voltei e vi que ninguém postou uma solução VBA ainda. Pensei em colocar um lá fora.

'indexes of the values stored as an array in the collection object
  Private Const USERNAME As Integer = 0
  Private Const DATETIME As Integer = 1


'references to where the data is or should be in the workbook
Public Enum DataColumns
    DateTimeStamp = 1
    UName = 2
    Department = 3
    LastUpdater = 4     'The information we will be adding!
End Enum


Sub Main()
    Dim lastUserByDept As Collection
    Set lastUserByDept = GetLastUpdater(2)
    AppendLastUserName 2, lastUserByDept
End Sub


'//Builds a collection of department entries, and stores
'//the last date along with the user tied to that date
Private Function GetLastUpdater(dataStartRow As Long) As Collection
    Dim currRow As Integer: currRow = dataStartRow


Dim maxDatesByDept As Collection
Set maxDatesByDept = New Collection

Dim deptInfo As Variant
Do While Not IsEmpty(Cells(currRow, DataColumns.DateTimeStamp))
    Dim dept As String: dept = Cells(currRow, DataColumns.Department).Value
    If DeptExists(maxDatesByDept, dept) Then
        If Cells(currRow, DataColumns.DateTimeStamp).Value > maxDatesByDept.Item(dept)(DATETIME) Then
            deptInfo = Array(Cells(currRow, DataColumns.UName).Value, Cells(currRow, DataColumns.DateTimeStamp).Value)
            UpdateExistingEntry maxDatesByDept, deptInfo, Cells(currRow, DataColumns.Department)
        End If
    Else
        deptInfo = Array(Cells(currRow, DataColumns.UName).Value, Cells(currRow, DataColumns.DateTimeStamp).Value)
        maxDatesByDept.Add deptInfo, Cells(currRow, DataColumns.Department).Value
    End If

    currRow = currRow + 1
Loop

Set GetLastUpdater = maxDatesByDept
Set maxDatesByDept = Nothing


End Function


'//Since we are using the VBA collection object, there is no true
'//test for if an element exists; the collection will just throw
'//an error if you ask it for something it cannot find, so just
'//trap the error and return false in that case, as it means no
'//item was found in the list with that dept as it's key
Private Function DeptExists(ByRef deptList As Collection, dept As String) As Boolean
    On Error GoTo handler
    deptList.Item dept
    DeptExists = True
    Exit Function
handler:
    Err.Clear
    DeptExists = False
End Function


'//Updates an existing entry in our collection of dept users.
'//Note: this implementation allows for the trapping of failed attempts
'//but is not used in this version to keep it as straight-forward as
'//possible - If it was important to know when such attempts failed, you
'//could trap on the return value of this method and take the appropriate
'//action.
Private Function UpdateExistingEntry(ByRef deptList As Collection, ByVal deptInfo As Variant, ByVal dept As String) As Boolean
    On Error GoTo handler


If DeptExists(deptList, dept) Then
    deptList.Remove dept
    deptList.Add deptInfo, dept
    UpdateExistingEntry = True
Else
    UpdateExistingEntry = False
End If
Exit Function


handler:
    Err.Clear
    UpdateExistingEntry = False
End Function


'//Uses the created collection of dept, username to add the
'//required username to the column
Private Sub AppendLastUserName(dataStartRow As Long, deptListing As Collection)
    Dim currRow As Integer: currRow = dataStartRow
    Do While Not IsEmpty(Cells(currRow, DataColumns.DateTimeStamp))
        Dim currDept As String: currDept = Cells(currRow, DataColumns.Department)
        Cells(currRow, DataColumns.LastUpdater).Value = deptListing(currDept)(USERNAME)
        currRow = currRow + 1
    Loop
End Sub

Este código colocará o nome de usuário da última pessoa que atualizou o sistema dentro do contexto do departamento.

Observe que isso pressupõe algumas coisas, como a localização das colunas; Há uma Enumeração sendo usada para fazer referência às colunas relevantes, para que você possa apontá-las para os índices de coluna apropriados se eles diferirem do exemplo e tudo funcionar conforme o esperado. Também assume que a coluna que contém o carimbo de data não possui lacunas e é sempre uma data.

Copie e cole todo o conjunto de código em um único módulo no Excel e ele funcionará perfeitamente.