Identifying Entries of Multiple People by Time

[FrenchCelt]

Hello,

I'm trying to create a macro that will boil down a huge dataset into a simple table that identifies the last 1st activity of everyone in a department and then the 1st last activity.

If that doesn't make sense, I'll try to clarify by giving an example of what the data looks like:

Date Created

User

Department

Apr 29, 2020 6:19:29 AM	user1	of_1
Apr 29, 2020 6:20:36 AM	user1	of_1
Apr 29, 2020 6:21:22 AM	user1	of_1
Apr 29, 2020 6:21:40 AM	user1	of_1
Apr 29, 2020 6:24:44 AM	user1	of_1
Apr 29, 2020 6:25:07 AM	user1	of_1
Apr 29, 2020 6:25:42 AM	user1	of_1
Apr 29, 2020 6:26:14 AM	user1	of_1

Apr 29, 2020 6:26:51 AM	user2	of_1
Apr 29, 2020 6:27:54 AM	user2	of_1
Apr 29, 2020 6:28:40 AM	user2	of_1
Apr 29, 2020 6:29:00 AM	user2	of_1
Apr 29, 2020 6:29:51 AM	user2	of_1
Apr 29, 2020 6:29:54 AM	user2	of_1
Apr 29, 2020 6:30:24 AM	user2	of_1

The data goes on for quite a while, with several other users and 3 other departments. This is basically a list of all the work they did, so I want to be able to display who was the last person to start working and the first person to stop working for a given day. If I wanted to do it manually, I might filter by individual and color the cell of every first entry yellow and the cell of every last entry red and then filter for all the yellows and identify the last one chronologically and copy the date/time data and user to the table, then repeat for the first one chronologically for all the reds, but I can't figure out how to do something like that in VBA.

Does anyone have any suggestions?

 

[mikerickson]

You could convert all that data into a table and then create a Pivot Table from that

user name would be the column for the pivot table, while the values would be MIN of the start times.

That would give you a Pivot Table showing each worker against the time that they started working. =MAX of that would give you the latest starting time.


Similarly to find the first stop time.

 

[FrenchCelt]

This looks promising. I've tinkered around a bit and created a workable pivot table and will set about coding it in VBA to automate the process. Thanks for the suggestion!