Returning value next to the nth occurrence of a particular number.

[daikontim]

Hi,
I have 2 columns. Column A has numbers, some of which are repeated
numerous times. Column B has dates. How do I look up the nth occurrence
of a particular number in column A and return the corresponding date? Any
help greatly appreciated.

[Ron Rosenfeld]

On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
<daikontim@discussions.microsoft.com> wrote:

>Hi,
>I have 2 columns. Column A has numbers, some of which are repeated
>numerous times. Column B has dates. How do I look up the nth occurrence
>of a particular number in column A and return the corresponding date? Any
>help greatly appreciated.

Try the array formula:

=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,num)+1-n))

entered by holding down <ctrl><shift> while hitting <enter>. Excel will place
braces {...} around the formula.

rng is the range with numbers in Column A
num is the particular number you are searching for.
n is the occurrence number
dts is the range of dates in column B


--ron

[Max]

One way ..

A sample construct is available at:
http://cjoint.com/?cvdLnPptlQ
Returning value next to the nth occurrence of a particular
number_daikontim_wks.xls

Assuming numbers and dates are in A1:B6
Put in C1: =IF(A1="","",COUNTIF($A$1:A1,A1))
Copy down to C6
Col C provides the occurence count for the numbers in col A

Assuming F1:F2 will contain the inputs for the number / occurence, and F3 is
where we want the result (corresp. date) to be

Put in F3, and array-enter (press CTRL+SHIFT+ENTER):
=INDEX(B1:B6,MATCH(1,(A1:A6=F1)*(C1:C6=F2),0))
Format F3 as date

Adapt the ranges to suit ..
(note that we can't use entire col refs)

Perhaps better with some minimal error trapping built-in, we could put
instead in F3, and array-enter the formula (as before):
=IF(OR(F1="",F2=""),"",INDEX(B1:B6,MATCH(1,(A1:A6=F1)*(C1:C6=F2),0)))

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"daikontim" <daikontim@discussions.microsoft.com> wrote in message
news:4D2F2F16-E4E7-4EB7-8B25-EFBC4CD89ED8@microsoft.com...
> Hi,
> I have 2 columns. Column A has numbers, some of which are repeated
> numerous times. Column B has dates. How do I look up the nth
occurrence
> of a particular number in column A and return the corresponding date?
Any
> help greatly appreciated.
>

[neillcato]

On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
<daikontim@discussions.microsoft.com> wrote:

>Hi,
>I have 2 columns. Column A has numbers, some of which are repeated
>numerous times. Column B has dates. How do I look up the nth occurrence
>of a particular number in column A and return the corresponding date? Any
>help greatly appreciated.

Try the array formula:

=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,num)+1-n))

entered by holding down <ctrl><shift> while hitting <enter>. Excel will place
braces {...} around the formula.

rng is the range with numbers in Column A
num is the particular number you are searching for.
n is the occurrence number
dts is the range of dates in column B


--ron

Ron's solution is outstanding

Here is a small change that will allow the range definitions rng and dts to be moved from row 1 - same instructions as in Ron's post

{=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,num)+1-n)-MIN(ROW(rng))+1)}

Neill

[Ron Rosenfeld]

On Tue, 21 Feb 2006 00:31:31 -0600, neillcato
<neillcato.23kgoa_1140503701.3525@excelforum-nospam.com> wrote:

>
>Ron Rosenfeld Wrote:
>> On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
>> <daikontim@discussions.microsoft.com> wrote:
>>
>> >Hi,
>> >I have 2 columns. Column A has numbers, some of which are repeated
>> >numerous times. Column B has dates. How do I look up the nth
>> occurrence
>> >of a particular number in column A and return the corresponding date?
>> Any
>> >help greatly appreciated.
>>
>> Try the array formula:
>>
>> =INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,num)+1-n))
>>
>> entered by holding down <ctrl><shift> while hitting <enter>. Excel
>> will place
>> braces {...} around the formula.
>>
>> rng is the range with numbers in Column A
>> num is the particular number you are searching for.
>> n is the occurrence number
>> dts is the range of dates in column B
>>
>>
>> --ron
>
>Ron's solution is outstanding
>
>Here is a small change that will allow the range definitions rng and
>dts to be moved from row 1 - same instructions as in Ron's post
>
>{=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,num)+1-n)-MIN(ROW(rng))+1)}
>
>
>Neill

Thanks for the addition
--ron