Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Need number of Saturdays and number of Sundays between 2 dates | Excel Worksheet Functions | |||
Need help Returning the cell addy/index of the first non-zero number | Excel Worksheet Functions | |||
Max number of Occurrence | Excel Worksheet Functions |