code not unique find latest date

[Barbara Wiseman]

I have a list of codes in column K, and their dates in column N. Some of
the codes are duplicated in column K. What I would like to do is in another
column pick the code from column K, but only if it is unique, or if not only
if it has the latest date in column N of all instances of that code.



e.g.



Col K Col N other column

X123 1-Jan-2004

X126 1-Feb-2004 X126

X123 1-Dec-2005 X123



The first example is blank as it is not unique and there is another instance
with the same code, with a later date. The 3rd example is not unique, but
does have the latest date of that code. The 2nd example is a unique code.



I have got this far

=IF(COUNTIF(K:K,K6)>1, ,K6)



which is the easy bit, I know. So I test for uniqueness and if the code is
unique I return the code. But how to fill in the blank to look at all the
instances of the code if it is not unique, there may be up to 2, 3 or 4
instances of some codes, and determine which has the latest date, and only
put the code in the 'other column' if it is the latest dated of all the
instances of that code?



It may not be possible, but any suggestions would be welcome. I am quite
happy to use a helper column if needed.



Thanks,

Barbara

 

[Domenic]

Enter the following formula in, let's say P1, and copy down:

=IF(COUNTIF($K$1:$K$100,K1)>1,IF(N1=MAX(IF($K$1:$K$100=K1,$N$1:$N$100)),K
1,""),K1)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the
ranges accordingly.

Hope this helps!

 

[Domenic]

Actually, if your dates in Column N are listed in ascending order, you
can use the following formula instead...

=IF(COUNTIF($K$1:$K$100,K1)>1,IF(N1=LOOKUP(2,1/($K$1:$K$100=K1),$N$1:$N$1
00),K1,""),K1)

....confirmed with just ENTER.

 

[Barbara Wiseman]

Domenic,
Thank you so much, with a little tweaking this worked perfectly.
Actually as there are thousands of codes I amended the references to K:K etc
and it seems to work with out being an array formula (i.e. no curly brackets
and only enter to confirm)

Grateful thanks from a frosty, misty, Hampshire, England,
Barbara