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

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.

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

On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
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,nu m)+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

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

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

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

Ron Rosenfeld Wrote:
On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
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,nu m)+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,n um)+1-n)-MIN(ROW(rng))+1)}


Neill


--
neillcato
------------------------------------------------------------------------
neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
View this thread: http://www.excelforum.com/showthread...hreadid=514606

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

On Tue, 21 Feb 2006 00:31:31 -0600, neillcato
wrote:


Ron Rosenfeld Wrote:
On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
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,nu m)+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