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

Question

Eu tenho dados gerados a partir do sistema que se parece 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 é no Excel, você poderia usar uma "fórmula de matriz".

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

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

Essa 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 da linha em que 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 de departamento) e anexando-o ao texto "B" (a coluna que contém os nomes de usuários), criando assim toda a referência de célula "número da letra +".

Note que isto precisará ser uma função de array, o que significa que você deve pressionar Ctrl + Deslocar + Enter ao sair do célula depois de digitar a fórmula em vez de apenas digitar como faria normalmente. Quando você fizer isso corretamente, a fórmula será envolvida em chaves como é mostrado acima. Se não houver chaves que envolvem 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 mais elegante de fazer isso, mas se você precisar apenas de uma solução rápida e suja, isso funcionará.

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

    

Answer 2

Voltou e viu 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
 

 Função final 

 '// Como estamos usando o objeto de coleção VBA, não há verdade
// testa para se um elemento existe; a coleção só vai jogar
'// um erro se você pedir algo que não pode encontrar, então apenas
// interceptar o erro e retornar falso nesse caso, pois significa que não
'// item foi encontrado na lista com esse departamento como chave
Função Privada DeptExists (ByRef deptList como Collection, dept As String) As Boolean
    No manipulador Error GoTo
    deptList.Item dept
    DeptExists = True
    Sair da função
manipulador:
    Err.Clear
    DeptExists = False
Função final 

 '// Atualiza uma entrada existente em nossa coleção de usuários do departamento.
'// Nota: esta implementação permite o trapping de tentativas falhadas
'// mas não é usado nesta versão para mantê-lo tão simples quanto
// possível - Se era importante saber quando tais tentativas falharam, você
'// poderia capturar o valor de retorno desse método e obter o valor apropriado
'//açao.
Função privada UpdateExistingEntry (ByRef deptList como coleção, ByVal deptInfo como Variant, ByVal dept como String) como Boolean
    No manipulador Error GoTo 

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

 manipulador:
    Err.Clear
    UpdateExistingEntry = False
Função final 

 '// Usa a coleção criada de dept, username para adicionar o
'// requer nome de usuário para a coluna
Private Sub AppendLastUserName (dataStartRow como longo, deptListing como coleção)
    Dim currRow As Integer: currRow = dataStartRow
    Do While Not IsEmpty (Células (currRow, DataColumns.DateTimeStamp))
        Dim currDept As String: currDept = Células (currRow, DataColumns.Department)
        Células (currRow, DataColumns.LastUpdater) .Value = deptListing (currDept) (NOME DE USUÁRIO)
        currRow = currRow + 1
    Loop
End Sub
  

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

Observe que isso pressupõe algumas coisas, como os locais das colunas; Há uma Enumeração sendo usada para referenciar as colunas relevantes, então você pode apontá-las para os índices de coluna apropriados se elas diferirem do exemplo e todas funcionarem conforme o esperado. Ele também pressupõe que a coluna que contém o registro de data não possui intervalos e é sempre uma data.

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