Keeping the First Record of Each Group

Below are access records of a website’s each section, you want to find the last access record of each user.

Firstly, sort the data:

esCalc_excel_keep_record_1

After sorting, the first record of each user is the user’s last access record. In order to get the first record of each user by filtering, enter formulas in column D to check if a user ID changes:

esCalc_excel_keep_record_2

Then, filter the records to keep only those that have FALSE in column D. The result is shown below:

esCalc_excel_keep_record_3

To calculate the maximum value for each of the multiple groups in Excel, in addition to monitor the change of users after sorting, you can also perform group and aggregate to obtain the last access time of each user, add a computed column and check if each access time is equal to the last access time, and then perform filter. Obviously, this method is more complicated.

Because the filtering criteria for different users differ, such a problem has no direct solution with Excel. Users will have to approach the problem from a different angle by introducing intermediate variables as the filtering criteria. Such a solution needs a change of thinking, is not intuitive and increases the difficulty in solving the problem.

Group data:

esCalc_excel_keep_record_4

Filter data:

esCalc_excel_keep_record_5

With esCalc, the desktop BI software, a filtering expression will be automatically and intelligently migrated according to various positions. Therefore, you can conveniently keep the record with the maximum value for each group and meet the complicated filtering requirement. Additionally, you can remove or hide the ineligible records as needed.