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 |
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 |
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 |
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 |
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 |
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 |
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Need to make a minor change. I'll post it shortly...
In article , Domenic wrote: 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 |
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Minor change...
Replace B2+1 with ROWS(Data)+1 Hope this helps! In article , Domenic wrote: 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! |
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Hi Domenic,
Thank you for your time and assistance. Your Formulae does work but I forgot to say the Dynamic Named Range "Data" spans many columns. Based on the orginal scenario I would like to have the relevant Row Number from these columns also returned. Can your Formulae be adjusted to facilitate this. Apologies for incomplete explanation. Cheers, Sam Domenic wrote: Minor change... Replace B2+1 with ROWS(Data)+1 Hope this helps! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Let's assume that Sheet1, Columns A, B, and C, starting at Row 2,
contains the data, let E2 contain the value of interest, such as 30, then try the following... Select F2 Insert Name Define Name: Data2 Refers to: =Sheet1!A$2:INDEX(Sheet1!A$2:A$65536,MATCH(9.99999 999999999E+307,Sheet1!A $2:A$65536)+1) Click OK Change the references accordingly. Then, enter the following formula in F2, and copy across: =LOOKUP(2,1/(FREQUENCY(IF(Data2=$E2,ROW(Data2)-MIN(ROW(Data2))+1),IF(Data 2<$E2,MATCH(ROW(Data2),ROW(Data2),0),ROWS(Data2)+ 1))=2),ROW(Data2))-1 ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article <62d9abfc700a8@uwe, "Sam via OfficeKB.com" <u4102@uwe wrote: Hi Domenic, Thank you for your time and assistance. Your Formulae does work but I forgot to say the Dynamic Named Range "Data" spans many columns. Based on the orginal scenario I would like to have the relevant Row Number from these columns also returned. Can your Formulae be adjusted to facilitate this. Apologies for incomplete explanation. Cheers, Sam |
Return Row Number of LAST Numeric Consecutive Duplicate in Column
Hi Domenic,
Thank you very much for all your help. The Formulae works Great! Cheers, Sam Domenic wrote: Let's assume that Sheet1, Columns A, B, and C, starting at Row 2, contains the data, let E2 contain the value of interest, such as 30, then try the following... Select F2 Insert Name Define Name: Data2 Refers to: =Sheet1!A$2:INDEX(Sheet1!A$2:A$65536,MATCH(9.9999 9999999999E+307,Sheet1!A $2:A$65536)+1) Click OK Change the references accordingly. Then, enter the following formula in F2, and copy across: =LOOKUP(2,1/(FREQUENCY(IF(Data2=$E2,ROW(Data2)-MIN(ROW(Data2))+1),IF(Data 2<$E2,MATCH(ROW(Data2),ROW(Data2),0),ROWS(Data2) +1))=2),ROW(Data2))-1 ...confirmed with CONTROL+SHIFT+ENTER. Hope this helps! Hi Domenic, [quoted text clipped - 9 lines] Cheers, Sam -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200607/1 |
All times are GMT +1. The time now is 03:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com