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

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

--
Biff
Microsoft Excel MVP


"Help on formula" <Help on wrote in
message ...
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

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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Help on formula" <Help on wrote in
message ...
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

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" wrote:

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

--
Biff
Microsoft Excel MVP


"Help on formula" <Help on wrote in
message ...
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

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!

--
Biff
Microsoft Excel MVP


"Help on formula" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Help on formula" <Help on wrote in
message ...
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

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

"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Help on formula" wrote in message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Help on formula" <Help on wrote in
message ...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Help on formula" wrote in message
...
Thank you very much from the bottom of my hart! These changes did solve
the
problem. Best wishes and salutation to you.

"T. Valko" wrote:

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!

--
Biff
Microsoft Excel MVP


"Help on formula" wrote in
message
...
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" wrote:

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

--
Biff
Microsoft Excel MVP


"Help on formula" <Help on wrote in
message ...
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

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Help on formula" <Help on wrote in
message ...
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