Getting Rid of All Rows Except the One for the Latest Date

by Allen Wyatt
(last updated December 14, 2016)

4

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:

  1. Select a cell within your data.
  2. Display the Data tab of the ribbon.
  3. Click the Sort tool. Excel displays the Sort dialog box.
  4. Using the controls in the dialog box, indicate that you want to sort first by facility (A to Z or Smallest to Largest, whichever is appropriate) and then by inspection date (Newest to Oldest). (See Figure 1.)
  5. Figure 1. The Sort dialog box.

  6. Click OK to actually sort the data.
  7. With the Data tab of the ribbon still visible, click the Remove Duplicates tool in the Data Tools group. Excel displays the Remove Duplicates dialog box.
  8. Make sure that the only field selected in the dialog box is the one that contains the facility. (See Figure 2.)
  9. Figure 2. The Remove Duplicates dialog box.

  10. Click OK. Excel removes the duplicates and leaves only those records that contain the latest (most recent) inspection date.

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:

=MAX(($A$2:$A$123=E2)*$B$2:$B$123)

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:

=SUMIFS($C$2:$C$123,$A$2:$A$123,E2,$B$2:$B$123,F2)

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.) As you add more data to your inspection list, your "result table" is updated to always show the latest inspection results.

Figure 3. A dynamic list of the latest inspecition results.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13125) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Editing a Hyperlink

Excel will cheerfully keep track of all sorts of hyperlinks for you. If you want to change the hyperlink in some way, don't ...

Discover More

Anchoring Comment Boxes in Desired Locations

Want your comment boxes to appear someplace other than the right side of a cell? You may be out of luck, and here's why.

Discover More

Counting the Times a Worksheet is Used

Do you need to know how many times a worksheet has been used? Excel doesn't track that information, but you can develop some ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Preparing Data for Import into Access

When importing Excel information into Access, you need to be concerned with the condition of the data. Here's how to make ...

Discover More

Scroll Wheel Doesn't Work when Editing

Using the mouse's scroll wheel can help improve how you edit information in a worksheet. Here's how to make sure that the ...

Discover More

Inserting a Radical Symbol

The radical symbol is used frequently in some branches of mathematics. If you want to insert a radical symbol in a cell, ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2017-02-07 10:38:38

kavita

What if Rating in some cells is missing and I want to print the next most recent rating? in my data, column A is employee Id, B is the performance rating and 3rd is actual rating. Emp Id appears two three or sometimes five times making up groups, column b has all the dates filled in but the rating column isn't completely filled in. I'd really appreciate the workaround to get the most recent date for which there is a rating in column C.. thank you!


2016-12-14 09:51:06

Sue

In Ken's solution using the formula in column D to filter the list, if you compare two COUNTIF statements, down to the current row and for the whole list, on the facility, it will filter on a growing list of inspections added to the end of the list as they happen without the need to sort it each time by facility and date. If the two COUNTIF statements have the same result that will be the last entry for that facility in the list and therefore the latest, if they are different the line can be filtered out.


2016-12-14 07:17:30

frank mccraw

I believe most analysts who read this post would blanch at the idea of destroying historical data, which would otherwise permit time series analysis of each facility from within a single spreadsheet database. The "extract" and "dynamic" display options are far superior to any "delete previous" data methods, as Allen Wyatt hints.


2014-06-02 03:12:47

Ken

Another idea that also keeps all the data, is to have in column 'D' a formula that detects when the facility value changes.
Assuming once again that the sorting has been done as suggested above, (by facility the most recent at the top) then in column 'D' starting at D2 do an IF formula like IF(A2=A1,"SAME","DIFF") and copy that down ... which means that whenever the facility value in this row is different to the facility in the row above you get "DIFF" in column 'D' ... then just filter on column 'D' for those rows that have the word "DIFF" in them.


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.