code not unique find latest date

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

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!

In article <[email protected]>,
"Barbara Wiseman" <[email protected]> wrote:

> 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

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.

In article <[email protected]>,
Domenic <[email protected]> wrote:

> 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!
>
> In article <[email protected]>,
> "Barbara Wiseman" <[email protected]> wrote:
>
> > 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,
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


"Domenic" <[email protected]> wrote in message
news:[email protected]...
> 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!
>
> In article <[email protected]>,
> "Barbara Wiseman" <[email protected]> wrote:
>
>> 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