View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

Hi Domenic,

Thank you very much - that's Brilliant!

=--(MMULT(--(ISNUMBER(SEARCH(Sheet2!$A4,Results))),TRANSPOSE(C OLUMN(Results)^0))0)
...and enter the following formula in E4, copy across and down...


=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(Array)),SUM(LARGE(IF(Array,ROW(Results
)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(1,Array,0)-1,"")


...confirmed with CONTROL+SHIFT+ENTER.


Cheers,
Sam




Domenic wrote:
Hi Domenic,

1) Can you provide a sample of the values for the first row in 'Results'?


09-18-36-46-56-66 (Sheet1)


Oh I see! This is one value, entered in one cell. I re-read your
original post and I see I missed that point. Sorry about that, Sam! In
that case, change the defined reference to...

=--(MMULT(--(ISNUMBER(SEARCH(Sheet2!$A4,Results))),TRANSPOSE(C OLUMN(Resul
ts)^0))0)

...and enter the following formula in E4, copy across and down...

=CHOOSE(2+SIGN(COLUMNS($E4:E4)-SUM(Array)),SUM(LARGE(IF(Array,ROW(Results
)-MIN(ROW(Results))+1),{0,1}+COLUMNS($E4:E4))*{1,-1})-1,MATCH(1,Array,0)-
1,"")

...confirmed with CONTROL+SHIFT+ENTER. Does this help?


--
Message posted via http://www.officekb.com