nth Unique Value in List

[[email protected]]

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

[Sheila D]

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


"[email protected]" wrote:

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

[[email protected]]

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


Sheila D wrote:
> 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
>
>
> "[email protected]" wrote:
>
> > 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
> >
> >

[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


<[email protected]> wrote in message
news:[email protected]...
> 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
>

[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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:OSNj2o%[email protected]...
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[[email protected]]

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 wrote:
> 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
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:OSNj2o%[email protected]...
> > 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
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> 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
> >>
> >
> >

[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


<[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>> 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
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:OSNj2o%[email protected]...
>> > 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
>> >
>> >
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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
>> >>
>> >
>> >
>

[[email protected]]

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 wrote:
> 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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >> 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
> >>
> >>
> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> >> news:OSNj2o%[email protected]...
> >> > 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
> >> >
> >> >
> >> > <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> 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
> >> >>
> >> >
> >> >
> >

[Bernie Deitrick]

Brett,

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

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>> 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
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 wrote:
>> >> 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
>> >>
>> >>
>> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> >> news:OSNj2o%[email protected]...
>> >> > 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
>> >> >
>> >> >
>> >> > <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> 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
>> >> >>
>> >> >
>> >> >
>> >
>

[[email protected]]

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 wrote:
> Brett,
>
> Reply to me, change dot to . , the at to @, and take out the spaces...
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> >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 wrote:
> >> 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
> >>
> >>
> >> <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 wrote:
> >> >> 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
> >> >>
> >> >>
> >> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> >> >> news:OSNj2o%[email protected]...
> >> >> > 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
> >> >> >
> >> >> >
> >> >> > <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> 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
> >> >> >>
> >> >> >
> >> >> >
> >> >
> >

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
>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 wrote:
>> Brett,
>>
>> Reply to me, change dot to . , the at to @, and take out the spaces...
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> >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 wrote:
>> >> 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
>> >>
>> >>
>> >> <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 wrote:
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> >> >> news:OSNj2o%[email protected]...
>> >> >> > 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
>> >> >> >
>> >> >> >
>> >> >> > <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> 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
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >
>> >
>