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.
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.
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.
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.
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.
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 ID | Inspection date | Equipment | Status |
|---|---|---|---|
| LT-014 | 2026-10-19 | Lighting timer | Follow-up |
| HV-009 | 2026-10-29 | Air handling unit | Cleared |
| PU-027 | 2026-10-11 | Transfer pump | Monitor |
| LT-014 | 2026-11-06 | Lighting timer | Cleared |
| HV-009 | 2026-11-02 | Air handling unit | Parts ordered |
| PU-027 | 2026-11-08 | Transfer pump | Cleared |
| DL-032 | 2026-11-01 | Loading dock lift | Monitor |
| LT-014 | 2026-10-30 | Lighting timer | Parts ordered |
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.