Select Newest Record and Create New Table of Unique Values in Excel
<p>I have an Excel table that records vehicle progress through work stations within a business. A given work station may be visited more than once.</p>

<p>The vehicle license plate number (Reg No) is used to identify the vehicle, and the user adds a new row to the spreadsheet when the vehicle arrives at the next new work station. As well as the Reg No, each row also has a Record ID.</p>

<p>This workflow means that multiple record rows will be created for a given Reg No.</p>

<p>When all work on a vehicle is complete, the record is cut and archived on another worksheet.</p>

<p><a href="https://i.stack.imgur.com/zOliU.png" rel="nofollow noreferrer">Current Vehicle Table</a></p>

<p>What I want to create is a summary table on another worksheet tab, that displays all rows for vehicles in progress. Where a vehicle currently has a single record, I want to extract that record row, and where a vehicle has multiple records, I want to extract only the last (most recent) record row. </p>

<p>I want the summary to be a “live” reflection of the underpinning data sheet.</p>

<p>From searches I have found formula examples to <a href="https://stackoverflow.com/questions/13307927/ignore-duplicates-and-create-new-list-of-unique-values-in-excel">Ignore Duplicates and Create New List of Unique Values in Excel</a> but these pick the first duplicate value by default, not the last. Search results on “lookup last match” or “return last value” have in common that the user must define the item they are searching for.</p>

<p>I think I need something different because my list of Reg No is not static - it is continually being refreshed by Reg No’s being added and removed (archived).</p>

<p>Acknowledging and understanding Excel is not a database, but if I was thinking in database / SQL terms, my (noob) query might be something like:
SELECT row
WHERE Reg No is unique
AND Record ID is greatest</p>

<p>Do you know of any way to achieve the result I seek, in Excel?</p>