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

by Allen Wyatt
(last updated October 19, 2019)

9

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.)

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.

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

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of ...

Discover More

Changing Space between the Footnote Separator and Footnotes

When you add footnotes to a document, Word separates those footnotes from the document body with a separator line. Here's ...

Discover More

Determining If a Value is Out of Limits

Need to figure out if a value is outside of some arbitrary limit related to a different value? There are a number of ways ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Copying a Cell without Formatting

When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy ...

Discover More

Ensuring Standard Units During Data Entry

Need to make sure that information entered in a worksheet is always in a given unit of measurement? It's not as easy of a ...

Discover More

Changing Months in a Workbook

When you copy a worksheet and then need to make changes to information in that worksheet (such as changing month names), ...

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 three minus 0?

2019-11-14 14:16:10

J. Woolley

@Jevgenijs (see 2019-10-31) wanted to keep track of when a row was last edited to enable identification of rows with the oldest changes. (A row is edited when any cell in that row is edited.) Several solutions have been provided in the comments below. All have a problem: No cell edit(s) can be Undone (Ctrl+Z) because Excel clears the Undo stack when a Sub procedure changes a cell's value. The previous solutions immediately store the date/time of each row's edit in a certain column of that row.
Here is a solution that postpones storing the date/time of each row's edit until the workbook is saved. This allows any cell edit(s) to be Undone prior to saving the workbook. For an applicable worksheet, EditDateColumn identifies the column for storing the date/time of each row's edit; pick a column (A=1, B=2, etc.) that does not have any other data.
1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click the applicable sheet and press F7 to open the Code pane, then paste the following VBA code (which applies only to the selected worksheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Const EditDateColumn As Long = 1
Call EditDate_Collect(Me, Target, Columns(EditDateColumn))
End Sub

3. Under VBAProject > Microsoft Excel Objects, click ThisWorkbook and press F7 to open the Code pane, then paste the following VBA code (which applies to all worksheets that have the code above):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call EditDate_Record(SaveAsUI, Cancel)
End Sub

4. Right-click VBAProject and pick Insert > Module, then paste the following VBA code into the new module:

Dim EditDates As New Collection

Public Sub EditDate_Collect(WS As Worksheet, Target As Range, EDColumn As Range)
Dim EditDate As Collection, LastRow As Long, A As Range
Dim msg As String, s As String, t As String
Const myName As String = "EditDate_Collect"
If EditDates.Count > 0 Then
LastRow = WS.UsedRange.Row - 1 + WS.UsedRange.Rows.Count
For Each A In Target.Areas
If (A.Row <= LastRow) And (A.Address = A.EntireRow.Address) Then
s = EDColumn.Address(False, False)
s = Left(s, (InStr(1, s, ":") - 1))
t = Target.Address(False, False)
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
msg = "To properly maintain edit date/time in column " & s & " of worksheet '" _
& WS.Name & "' it is necessary to save the workbook before" _
& " inserting, deleting, copying, or moving rows on that worksheet." _
& vbNewLine & vbNewLine & "Your latest change to range " & t _
& " has been nullified (undone)." _
& " Please save the workbook before repeating that change."
MsgBox msg, vbCritical, myName
Exit Sub
End If
Next A
End If
For Each A In Target.Areascolumns
If (Intersect(A, EDColumn) Is Nothing) And (A.Address <> A.EntireColumn.Address) Then
Set EditDate = New Collection
EditDate.Add Now, "Date"
EditDate.Add Intersect(A.EntireRow, EDColumn).Address, "Address"
EditDate.Add WS, "WS"
EditDates.Add EditDate
End If
Next A
End Sub

Public Sub EditDate_Record(SaveAsUI As Boolean, Cancel As Boolean)
Dim EditDate As Collection
Application.EnableEvents = False
For Each EditDate In EditDates
EditDate("WS").Range(EditDate("Address")) = EditDate("Date")
Next
Set EditDates = New Collection
Application.EnableEvents = True
End Sub

5. Be careful when inserting, deleting, copying, moving, or sorting columns on the worksheet. Save the workbook as XLSM or XLSB. For a formatted and commented version of this VBA code, see the Google Drive text file EditDate.txt at
https://drive.google.com/open?id=16CestQHL2GusGg-M5CkJHk5LVcVXQHiu


2019-11-05 12:27:06

J. Woolley

My last procedure works correctly only when CurrentDateColumn C is 1 (column A). Here is an improved version without that limitation.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, R As Range
Const C = 1 'CurrentDateColumn
Set R = Columns(C)
For Each A In Target.Areas
If (Intersect(A, R) Is Nothing) And (A.Rows.Count < 65536) Then
Range(Cells(A.Row, C), Cells((A.Row + A.Rows.Count - 1), C)) = Now
End If
Next A
End Sub

As before, there are still two problems with this procedure:
1. It ignores Areas that include CurrentDateColumn; this is probably acceptable.
2. No cell change(s) can be undone (Ctrl+Z); this is left as an exercise for the reader. For example, see
https://wellsr.com/vba/2019/excel/how-to-undo-a-macro-with-vba-onundo-and-onrepeat/


2019-11-04 13:13:00

J. Woolley

@Willy Vanhaelen
Here is an improved version of your macro. It handles discontinuous ranges and ignores changes involving more than 65535 rows (such as entire columns).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range
Const C = 1 'CurrentDateColumn
For Each A In Target.Areas
If A.Column <> C And A.Rows.Count < 65536 Then
Range(Cells(A.Row, C), Cells((A.Row + A.Rows.Count - 1), C)) = Now
End If
Next A
End Sub

There are two problems with this macro:
1. It ignores Areas that include CurrentDateColumn; this is probably acceptable.
2. No change(s) can be undone (Ctrl+Z); this is left as an exercise for the reader.


2019-11-04 05:23:52

Willy Vanhaelen

@J. Woolley
As I mentioned, my macro handles only adjacent cells (in a column) but it does no harm if you try discontinuous ranges. Only the first area of the selection will be handled and the others are ignored.

I did no effort to try to make the macro handle discontinuous ranges because in practice this will almost never be necessary.


2019-11-03 11:17:56

J. Woolley

@Willy Vanhaelen
What if Target is a discontinuous Range (like B1:B10,B21:B30)?


2019-11-02 13:26:12

Willy Vanhaelen

@J. Woolley
Your macro has a flaw. If you delete some row(s) in the sheet, the macro takes a very long time to complete because of the For Each ... Next loop. It runs for every cell in each row (16384) and finaly ends by overwriting the date in the following row(s).

Here is a macro that avoids this because it has no For Each loop and still handles entries in several adjacent cells with Ctrl Enter:

Private Sub Worksheet_Change(ByVal Target As Range)
Const C = 1 'Current date column
If Target.Column = C Then Exit Sub
Dim R As Integer: R = Target.Row
Range(Cells(R, C), Cells(R + Target.Rows.Count - 1, C)) = Now
End Sub


2019-11-01 15:07:21

J. Woolley

@Jevgenijs

The following VBA code will add the current date/time to a column whenever cells in a row of the worksheet are changed. The current date/time is put in CurrentDateColumn of the applicable row. CurrentDateColumn is set to 1 (column A), but you can specify any column you prefer. After sorting on that column, you can decide which rows to delete or move.

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Under VBAProject > Microsoft Excel Objects, click the applicable sheet and press F7 to open the Code pane, then paste the following VBA code (which applies only to the selected worksheet):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Const CurrentDateColumn = 1
For Each c In Target.Cells
If c.Column <> CurrentDateColumn Then
Cells(c.Row, CurrentDateColumn).Value = Now
End If
Next c
End Sub


2019-10-31 12:12:23

Jevgenijs

Hi Allen, i was wondering if you could help me. I m looking on a small sample of database, like 5000 entries on excel. Would it be possible to write a function for excel which would work like that IF row has not been edited in the last 19 month (or any time frame i need) then delete entry or move it to another page.

Many thanks


2019-10-31 11:30:56

Jevgenijs

Hi Allen, i was wondering if you could help me. I m looking on a small sample of database, like 5000 entries on excel. Would it be possible to write a function for excel which would work like that IF row has not been edited in the last 19 month (or any time frame i need) then delete entry or move it to another page.

Many thanks


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.