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

[Guest]

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]

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

 

[neillcato]

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>. Exce
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 an
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)


Neil

 

[Ron Rosenfeld]

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