ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   nth Unique Value in List (https://www.excelbanter.com/excel-discussion-misc-queries/103195-nth-unique-value-list.html)

[email protected]
nth Unique Value in List
 
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
nth Unique Value in List
 
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


" 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]
nth Unique Value in List
 
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


" 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
nth Unique Value in List
 
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


wrote in message
oups.com...
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
nth Unique Value in List
 
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
...
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


wrote in message
oups.com...
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]
nth Unique Value in List
 
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
...
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


wrote in message
oups.com...
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
nth Unique Value in List
 
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:A 15,A5:A15,FALSE)<(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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
...
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


wrote in message
oups.com...
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]
nth Unique Value in List
 
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:A 15,A5:A15,FALSE)<(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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
...
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


wrote in message
oups.com...
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
nth Unique Value in List
 
Brett,

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

HTH,
Bernie
MS Excel MVP


wrote in message
ups.com...
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:A 15,A5:A15,FALSE)<(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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
...
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


wrote in message
oups.com...
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]
nth Unique Value in List
 
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


wrote in message
ups.com...
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:A 15,A5:A15,FALSE)<(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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
...
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


wrote in message
oups.com...
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
nth Unique Value in List
 
I've sent a working example to your gmail account....

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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


wrote in message
ups.com...
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:A 15,A5:A15,FALSE)<(ROW(A5:A15)-ROW(A4)),100000,A5:A15),100000),6),A:A))

HTH,
Bernie
MS Excel MVP


wrote in message
oups.com...
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
...
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


wrote in message
oups.com...
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


All times are GMT +1. The time now is 08:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com