2

I have data generated from the system that looks like the table below. On the last column I need to display the user who was the last one updating the system from each dept.

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
3
  • 2
    What program are you using to view/edit this data?
    – Daniel Beck
    Jan 28, 2012 at 4:30
  • I recommend PL/S macros for this -- much more powerful than C macros. Jan 28, 2012 at 4:42
  • I am using excel 2010. hope my question and example is clear enough!
    – Christin
    Jan 28, 2012 at 5:34

2 Answers 2

2

IF I understand what you are asking, and this is in Excel, you could use an "array formula".

For example, if your dataset was in a worksheet in the range A1:C5, you could use this in column "D":

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

This formula will dynamically build the cell reference that will point to the required username by returning the row number for the row where the max date is found, but only for those rows where the department value matches the value of that row in column "C" (the department column), and appending it to the text char "B" (the column that contains the usernames), thus creating the whole "letter+number" cell reference.

Note that this will need to be an array function, which means that you must hold down Ctrl + Shift + Enter when leaving the cell after typing up the formula instead of just enter like one would normally do. When you do it right, the formula will be wrapped in curly brackets as it is shown above. If there are no curly brackets wrapping the formula, then it is not set up as an array formula, and will not work correctly.

There may be a simpler or more elegant way of doing this, but if you just need a quick and dirty solution, this will work.

If you need more of an explanation, I can provide more details.

1
  • Hi Daniel, Im using Excel, thank you for the respond
    – Christin
    Jan 28, 2012 at 13:01
0

Came back round, and see that no one has posted a VBA solution yet. Figured I would put one out there.

'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

This code will place the username of the last person to update the system within the context of the department.

Note that is assumes some things, such as the column locations; There is an Enumeration being used to reference the relevant columns, so you can point them to the proper column indexes if they differ from the example and all should work as expected. It also assumes that the column containing the datestamp has no gaps, and is always a date.

Copy and paste the entire set of code into a single module in excel and it will work just fine.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .