PDA

View Full Version : MAX date problem



sharky12345
08-10-2013, 02:43 AM
Can someone help me change this formula so that it puts the text 'DEVICE NOT USED' in place of any blank cells found?
=MAX(INDEX(('Device Use - 4 month Period'!$A$2:$A$20000=A2)*('Device Use - 4 month Period'!$C$2:$C$20000),))

Also posted elsewhere: http://www.mrexcel.com/forum/excel-questions/718967-find-latest-date-delete-older-data.html

SamT
08-10-2013, 08:10 AM
Thank you for the cross posting link.

Original question on Mr. Excel"
Can anyone suggest a VBA solution to help me with this? Column A has numbers, (up to row 20000), and column E has a date and time formatted as 'dd/mm/yyyy hh:ss'. I want to be able to search column A and remove all rows except the one with the latest date in column E, so in other words I am deleting all of the oldest entries. Can anyone suggest something that may help?

A simple VBA code to solve the original question is trivial. But... Which columns are you using for the latest date? "C" or "E"?

I'm not sure how to use your cell formula to effect Column "A", And I don;t even know which cell(s) it is in.


Sub LeaveLatest()
Dim i As Long
Dim MaxDate As Long

MaxDate = Max(Sheets("Device Use - 4 month Period").Range("E:E").Value)

With Sheets("Device Use - 4 month Period").Range("$A$2:$A$20000")
For i = .Cells.Count To 1 Step -1
If .Cells(i) = MaxDate Then .Cells(i).Delete Shift:=xlShiftUp
Next i
End With
End Sub

sharky12345
08-10-2013, 08:19 AM
SamT, thanks for your suggestion - I should have explained in the post how I got to using the MAX formula. I played around with ideas whilst waiting for a solution and came up with the MAX formula, which allows me to get the latest date without deleting any rows, (the data is copied from the source sheet to a destination sheet by the formula). What I need now to make this work perfectly is for the text 'DEVICE NOT USED' to be inserted if the date is not found, in other words if the source cell is blank because at the moment I am getting '00/00/1900 00:00' which is the default I believe.

Sorry for the confusion.....

SamT
08-10-2013, 09:40 AM
This all goes on one line in the Formula Bar. Obviously I can't test it. You may have to replace the two doublequotes at the end of the top snippet with the actual value returned when the date is not found.

=IF(MAX(INDEX(('Device Use - 4 month Period'!$A$2:$A$20000=A2)*('Device Use - 4 month Period'!$C$2:$C$20000),))=""
,"DEVICE NOT USED"
,MAX(INDEX(('Device Use - 4 month Period'!$A$2:$A$20000=A2)*('Device Use - 4 month Period'!$C$2:$C$20000),)))

You can make the formula in the Formula Bar shorter by Excel Menu >> Insert >> Name>> Define

Names In Workbook:="FindMax"

Refers To:="MAX(INDEX(('Device Use - 4 month Period'!$A$2:$A$20000=A2)*('Device Use - 4 month Period'!$C$2:$C$20000),))"

This would make the cell formula
'If(FindMax="","DEVICE NOT USED",FindMax)

Hint: You can also Name the Ranges and use those names in the Defined (Name) Max Formula

sharky12345
08-10-2013, 09:55 AM
I'll try your suggestion and will report back - I'm very grateful...

sharky12345
08-11-2013, 10:08 AM
Both your suggestion and the one in the linked post work and achieve the same thing so thank you!