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
|