Condensing thousands of rows of user data using dates

[ShamanWolf]

I have a csv format report showing user transactions during periods where the system didn't translate clockin information from a secondary payroll system.

This captures tens of thousands of rows of data, but I need to condense it by the username start time of the first transaction for that user and end time of the last transaction.

Format is
A. B. C. D. E.
Username|noncritical_system_entry|miscellaneous_data|start_date_time|end_date_time

Is there a way to search this data to leave only one entry per day per username with first start and last end transaction time; to make it more manageable to input clockin and clockout to ensure these users get credit for their system transactions?

The only values I need to keep are columns A D and E

Thanks in advance,
Shaman

[kersplash]

Can you attach a decent sample size showing your source data and what you expect as your output for that source mocked-up.

Go Advanced -> Manage Attachments -> Upload

[ShamanWolf]

Hi, sorry about the delay this is just a quick mockup but the before and after tabs show my need

[Roel Jongman]

A pivottable is a very fast method to find those results.

you only have to choose min and max and reformat to show date and time in the values section of the pivot for the fields tx start and tx end
I also inserted an extra column in the data to calculate total (net) duration (it adds up the times between each tx start and tx end line. for this example the end - start gives same result but I think in reality there can be a different outcome for end - start and adding tx duration line by line as there may be gaps between last end time and new starttime.