Help on formula

[Help on formula]

I have list of two columns A=Addresses and B=Dates. In addresses column some
addresses are duplicated (because the date is different). I want the most
resent dated address merge into one address. In other words of explaining is
to avoid the duplication of the same address, I want to keep the most resent
dated address on the list. Please help me with the formula so instead of
deleting the duplicated addresses manually by checking the recent dates
(which is very time consuming and inaccurate process) I can simply apply the
formula. Thank in advance

 

[T. Valko]

Here's one way...

Assume the range is A2:B20

Enter this array formula** in C2 and copy down to C20:

=B2=MAX(IF(A$2:A$20=A2,B$2:B$20))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

This will return TRUE for the most recent date for an address.

Now, select the range A2:C20
Goto the menu Data>Sort
Sort by column C descending

This will put all the old dates/addresses at the bottom of the list.

At the first row where the FALSES start, delete all those entries in columns
A:C.

Then delete all the remaining formulas in column C that returned TRUE.

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Help on formula]

Thank you very much T. Valko and Don for your reponse. You did solve my
problem so far. I noticed that some addresses in coloumn A having the same
addresses and dates and they duplicated more than once (same addresses with
same dates). Is there any adjustment that can be apply to the formula to keep
one address out of the matching addresses and dates also, because value turns
TRUE on more than one addresses that has the same dates

 

[T. Valko]

Change the formula to:

=AND(B2=MAX(IF(A$2:A$20=A2,B$2:B$20)),SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))=1)

Still array entered!

 

[Help on formula]

Thank you very much from the bottom of my hart! These changes did solve the
problem. Best wishes and salutation to you.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Don Guillett]

Macro supplied to OP

Option Explicit
Sub SortAndCullListSAS()
Dim lr As Long
Dim i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:C" & lr).Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("b2"), Order2:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
For i = lr To 2 Step -1
If Cells(i + 1, 1) = Cells(i, 1) Then
'=
If Cells(i + 1, 2) > Cells(i, 2) Then
Rows(i).Delete
Else
Rows(i + 1).Delete
End If
'=
End If
Next i
Application.ScreenUpdating = True
End Sub