How to Keep the Latest Record for Each ID in Excel

Open a copy of your Excel workbook (.xlsx) in Cue Office. Sort the dates from newest to oldest, use COUNTIF to mark the first row for each ID, and filter to show those rows. You can clear the filter whenever you need the full inspection history.

Sort by date and keep each row together

On Service Log, check that column B contains date values. Imported text can look just like a date, such as 2026-11-08. Use values the spreadsheet recognizes as dates for chronological sorting and date calculations. Then drag to select A1:D9, including the headers, so each date moves with its ID, equipment name, and status.

Go to Data → Sort → Custom Sort and check My data has headers. Under Sort by, choose Column B, set the order to Z → A, and click OK.

In the Sort dialog box, Column B is sorted Z to A, headers are checked, and OK is visible at the bottom
The sort dialog shows Column B, Z → A, and the selected header checkbox.

After sorting, check that the first record pairs 2026-11-08 with PU-027. If your most recent date is farther down, check the date type and the sort direction.

Mark the first record for each ID with COUNTIF

In E1, enter Keep latest. Select E2 in the grid and paste the following eight formula lines into E2:E9.

=COUNTIF($A$2:A2,A2)=1
=COUNTIF($A$2:A3,A3)=1
=COUNTIF($A$2:A4,A4)=1
=COUNTIF($A$2:A5,A5)=1
=COUNTIF($A$2:A6,A6)=1
=COUNTIF($A$2:A7,A7)=1
=COUNTIF($A$2:A8,A8)=1
=COUNTIF($A$2:A9,A9)=1

COUNTIF($A$2:A2,A2) counts how many times the current ID appears from the first data row through the current row. $A$2 fixes the start of the range while the end moves down. The =1 test returns TRUE the first time it finds an ID and FALSE when the ID appears again. Because the dates are sorted newest first, the first row for each ID is its latest record.

Status and Keep latest columns, showing TRUE in the first four rows and FALSE in the last four rows
Column E returns TRUE when an asset ID appears for the first time.

In your own workbook, use your ID column in place of column A. Start at the first data row and extend the formula through the last record. If the latest date is tied for an ID, decide which record takes priority using the inspection time or a sequence number, then put that record first.

Filter for TRUE and check the latest records

Select A1:E9 and click Data → Filter. Open the dropdown menu on the column E header, uncheck FALSE, leave TRUE checked, and click Confirm.

Filter dropdown menu with FALSE unchecked, TRUE checked, and Confirm at the bottom right
Select TRUE and clear FALSE. Click Confirm to hide the older rows.
Row numbers jump from 5 to 10, showing LT-014 with inspection date 2026-11-06Second record across equipment and status columns shows Lighting timer with Cleared status
Keep latest column filtered, showing TRUE across all four visible rows
The cropped views show the same rows in the same order. Click any image to see all five columns together.

Check LT-014, which has three inspection records. The visible row should show Lighting timer on 2026-11-06 with the status Cleared. Its older records are October 19 (Follow-up) and October 30 (Parts ordered). DL-032 has only one inspection, so that row stays visible.

Save and reopen the workbook. The four filtered records should still be visible. The row numbers jump from 5 to 10 because rows 6 through 9 are hidden. Clear the filter to see the full inspection history.

Write the latest records to a new sheet with Cue AI

For a shift handoff, use Cue AI to create a separate list. Open a copy of the original workbook and click Cue AI on the Home tab.

Cue AI toolbar button and expanded chat panel showing a maintenance log request
Send your request in the Cue AI panel. You can copy the exact prompt below.
Using Service Log!A1:D9, create a separate worksheet named Latest Records with columns Asset ID, Inspection date, Equipment and Status. Keep the complete row with the latest inspection date for each Asset ID, then sort the four result rows newest first. Preserve dates as dates with yyyy-mm-dd display; make all four columns readable. Leave the original Service Log worksheet unchanged. Apply the result in this workbook.

Send the prompt, then save and reopen the workbook. Check that Latest Records contains four rows in newest-first order. Switch back to Service Log and check that all eight original rows are still in the same order.

Columns A and B of Latest Records showing four asset IDs and inspection datesColumns C and D of Latest Records showing equipment and status, with the second row as Lighting timer and Cleared
Bottom of the workbook showing the Latest Records tab selected next to Service Log
The selected sheet is Latest Records. Check the ID, date, equipment, and status on the same row.

The new sheet is a snapshot of the log when you ran the request. Generate a new list after you add inspection records. The example stores dates such as 2026-11-08 as text. For calculations such as days since inspection, use the manually filtered records, which retain their date values.

Practice with eight maintenance records

Show the complete example

Paste the practice data starting at cell A1 in Service Log, check that column B contains date values, and follow the sorting steps in the tutorial. Among the four assets, only DL-032 appears once.

Asset IDInspection dateEquipmentStatus
LT-0142026-10-19Lighting timerFollow-up
HV-0092026-10-29Air handling unitCleared
PU-0272026-10-11Transfer pumpMonitor
LT-0142026-11-06Lighting timerCleared
HV-0092026-11-02Air handling unitParts ordered
PU-0272026-11-08Transfer pumpCleared
DL-0322026-11-01Loading dock liftMonitor
LT-0142026-10-30Lighting timerParts ordered
Original log showing four-column headers and eight maintenance records
The same asset ID can appear multiple times in the original log.

Which records should you check in the next handoff?

If the next shift adds an inspection for LT-014, give Cue AI the updated data range and a new sheet name to regenerate the handoff list. Check that the new date and status for LT-014 come from the same row. Then check DL-032, which had no new inspection, to make sure it still shows its previous status.

Use the free tools in Cue Office to open the PDF inspection form and verify the date for LT-014, then update the Excel log (.xlsx). Leave any follow-up for the next shift in a Word handoff note (.docx), or add the status change to a PowerPoint update (.pptx) for the next meeting.

Download Cue Office for free