nth Unique Value in List

[brett.kaplan]

Hi,

I'm trying to find the 6th latest unique date in a list of dates:

5/25/2006
5/26/2006
5/30/2006
5/30/2006
5/30/2006
5/30/2006
5/31/2006
6/1/2006
6/2/2006
6/3/2006
6/4/2006

So, I want the formula to return 6/2/2006, but using the large function
returns 5/30/2006. Is there a way to count 5/30/2006 only once instead
of 4 times. I really need to be able to do this in a single cell
formula.

Thanks,
Brett

 

[Guest]

The LARGE function does work on Unique values. Given your sample data the 6th
lates is 30/5/2006
1. 04/06/2006
2. 03/06/2006
3. 02/06/2006
4. 01/06/2006
5. 31/05/2006
6. 30/05/2006

Perhaps I have misunderstood your question?
Sheila

 

[brett.kaplan]

Sorry, I was counting up instead of down. Basically, I want the 6th
unique day from 5/25/06 given that list...

 

[Bernie Deitrick]

Brett,

Let's say that your Dates are in A2:A12. Array enter (enter using Ctrl-Shift-Enter) the formula

=SMALL(IF((MATCH(A2:A12,A2:A12,FALSE)*1)<>(ROW(A2:A12)-ROW(A1)),100000,A2:A12),6)

and format as a date.

Note that the A1 reference should always be the cell just above your list:

=SMALL(IF((MATCH(A5:A15,A5:A15,FALSE)*1)<>(ROW(A5:A15)-ROW(A4)),100000,A5:A15),6)

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

You don't need the *1 - that was an artifact from a failed first attempt...

=SMALL(IF(MATCH(A5:A15,A5:A15,FALSE)<>(ROW(A5:A15)-ROW(A4)),100000,A5:A15),6)

HTH,
Bernie
MS Excel MVP

 

[brett.kaplan]

This is great. Thanks!

A followup now, because I hardly ever use arrays so I'm not familiar
with them.

I have 2 columns: a list of every day in column A and a list of dates
that I want to convert them to in column B. Typically, I just vlookup
the date and return the value in column 2, however, this one requires
this array/offset to happen. So, is there a way to now say vlookup the
5/25/2006 from column A and then return 6/2/2006 (the result from the
below formula)? As a larger excerpt of the sheet, it looks like:


5/25/2006 5/25/2006
5/26/2006 5/26/2006
5/27/2006 5/30/2006
5/28/2006 5/30/2006
5/29/2006 5/30/2006
5/30/2006 5/30/2006
5/31/2006 5/31/2006
6/1/2006 6/1/2006
6/2/2006 6/2/2006
6/3/2006 6/3/2006

I can't change the values in column B (and don't want to create a new
column). So basically, I need to vlookup 5/25/2006, offset it by 6
unique days in column B, and return that value.

Is that possible??

Thanks!

 

[Bernie Deitrick]

Again, Array-entered.

First, put the lookup date (the 5/25/2006) into cell D3.

=INDEX(B:B,MATCH(SMALL(IF(A5:A15>=D3,IF(MATCH(A5:A15,A5:A15,FALSE)<>(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP

 

[brett.kaplan]

I am getting a value of 5/30/2006 when I try this one. Would you mind
if I e-mailed you my sheet so you could see if I'm doing something
wrong?

Thanks!

Brett

 

[Bernie Deitrick]

Brett,

Reply to me, change dot to . , the at to @, and take out the spaces...

HTH,
Bernie
MS Excel MVP

 

[brett.kaplan]

I tried to get to just you, but got a bounce back - can you email me
first:

brett dot kaplan at gmail dot com

Thanks! No spaces, replace words with symbols

 

[Bernie Deitrick]

I've sent a working example to your gmail account....

HTH,
Bernie
MS Excel MVP