Written by Allen Wyatt (last updated October 19, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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:
=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 Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only ...
Discover MoreIf you hide the first rows of a worksheet, you may have a hard time getting those rows visible again. Here's a simple way ...
Discover MoreWhen you insert cells into a worksheet, Excel needs to know which direction it should shift the displaced cells. If you ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments