by Allen Wyatt
(last updated April 25, 2019)
Gary is using an Excel worksheet to maintain of list of facilities that his company inspects, along with the dates of all the prior inspections of those facilities. This results in multiple rows for each facility, one row per inspection. Gary needs to delete all the rows for each facility with the exception of the latest inspection date. The result would be one row per facility, showing the latest inspection date.
Perhaps the easiest way to do this is to use Excel's remove duplicate tool. To use the tool for this particular purpose, follow these steps:
Figure 1. The Sort dialog box.
Figure 2. The Remove Duplicates dialog box.
Understand that if you follow these steps it is destructive to your data—when completed, the older data is completely removed from your worksheet. Thus, if you want to maintain the older information for historical purposes, you may want to perform the steps on a duplicate of your data.
Of course, you could also use a different approach that maintains the original data and simply extracts the information that represents the latest inspection dates. Assume, for the purposes of this example, that your data is in columns A:C, with A containing the facility, B containing the inspection date, and C containing the rating achieved on that date. Further, the first row of your data contains headings (Facility, Inspected, and Rating). Somewhere to the right of your data—separated by at least one empty column—place another set of identical headings. (For this example I'll assume that these appear columns E:G.)
In the first column place a unique list of your facilities. In cell F2 place the following formula:
You can replace the two lower range references ($A$123 and $B$123) with whatever lower range is appropriate for your data. Also, you need to enter this as an array formula, meaning you press Ctrl+Shift+Enter to add it to cell F2.
The result in cell F2 will be a number, which is actually a date. (Excel maintains dates internally as numbers.) To get F2 to look like a date, simply apply a date format to the cell.
In cell G2 place the following formula:
Again, the lower range references can be replaced with whatever reference is appropriate for your data. This is not an array formula, so you can simply press Enter to put it in cell G2.
Now copy cells F2:G2 down as many rows as appropriate for your facilities. What you end up with is a dynamic list of the most recent inspection results for each facility. (See Figure 3.)
Figure 3. A dynamic list of the latest inspection results.
As you add more data to your inspection list, your "result table" is updated to always show the latest inspection results.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13125) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
How you add special characters to Excel can differ from how you add them in other Office programs, such as Word. This tip ...Discover More
Do you need to paste formulas without updating the references in whatever you are pasting? You can accomplish this, ...Discover More
Using the mouse to select a large cell range can be frustratingly slow. If you want to make copying to a large range of ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.