Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Hi All,
Using a Dynamic Named Range "Data", I would like a Formula to return the Row Number of the "LAST" instance of a duplicate numeric value - repeating twice consecutively in the same column. Thanks Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Just to be clear, if A2:A10 contains the following data...
6 6 9 3 5 7 7 7 4 ....what would be your expected result? In article <62cefd3c3cc94@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi All, Using a Dynamic Named Range "Data", I would like a Formula to return the Row Number of the "LAST" instance of a duplicate numeric value - repeating twice consecutively in the same column. Thanks Sam |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Hi Domenic,
A column will contain multiple instances of the same numerical value, so A2: A10 will contain only numeric value 30. This numeric value will at times have consecutive duplicates (x2). Cheers, Sam Domenic wrote: Just to be clear, if A2:A10 contains the following data... 6 6 9 3 5 7 7 7 4 ...what would be your expected result? -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Are you saying that A2:A10 might look something like this... ?
30 30 30 30 30 If so, what result would you expect? And to be clear, will there ever be an instance where there's three or more consecutive values? In article <62d036ea3708e@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, A column will contain multiple instances of the same numerical value, so A2: A10 will contain only numeric value 30. This numeric value will at times have consecutive duplicates (x2). Cheers, Sam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Hi Domenic,
Domenic wrote: Are you saying that A2:A10 might look something like this... ? Yes 30 30 30 30 30 Row Number of LAST 30 with two consecutive instances If so, what result would you expect? And to be clear, will there ever be an instance where there's three or more consecutive values? I would expect the Row Number of the LAST 30 with two consecutive instances. There may be instances with three or more consecutive values BUT I only require the LAST Row Number of those with two consecutive instances. Cheers, Sam -- Message posted via http://www.officekb.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return Row Number of LAST Numeric Consecutive Duplicate in Column
First, assuming that Sheet1, Column A, starting at A2, contains the
data, change the reference for the defined named 'Data' to... =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(9.99 999999999999E+307,Sheet 1!$A$2:$A$65536)+1) Then, let B2 contain the number of interest, such as 30, and try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =LOOKUP(2,1/(FREQUENCY(IF(Data=B2,ROW(Data)-MIN(ROW(Data))+1),IF(Data<B2 ,MATCH(ROW(Data),ROW(Data),0),B2+1))=2),ROW(Data))-1 Note that if you'd like to check for the last instance of 3 consecutive values, change =2 to =3, and so on. Hope this helps! In article <62d0c6b91c44f@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Domenic wrote: Are you saying that A2:A10 might look something like this... ? Yes 30 30 30 30 30 Row Number of LAST 30 with two consecutive instances If so, what result would you expect? And to be clear, will there ever be an instance where there's three or more consecutive values? I would expect the Row Number of the LAST 30 with two consecutive instances. There may be instances with three or more consecutive values BUT I only require the LAST Row Number of those with two consecutive instances. Cheers, Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column picked randomly with probability relative to number of entr | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Running total w/2 columns - Excel | Excel Worksheet Functions |