Count Intervals of 2 Consecutive Values in same Row and Return Count across Row

[Sam via OfficeKB.com]

Hi All,

Dynamic Named Range "Results" spans 6 Columns and many Rows - starts at Row 2,
headings in Row 1.
One cell houses 6 Alpha-Numeric single-digit (entered with leading zero) or
double-digit values entered like 08-20-21-40-60-61 per row. Each value will
only appear once in any row and the values are listed in ascending order.

I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) of each individual
occurrence of any designated PAIR of values (single-digit/ double-digit) in
the same Row of the Named Range "Results" . The results of each calculated
INTERVAL of a designated PAIR returned across the same Row of the New Sheet
(i.e. each Row houses the Intervals for ONLY one designated pair of values) -
starting with the most recent (the LAST) occurrence.

For instance, each time 60 and 61 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.

The results are returned to a Table layout: I have the criterion vertically,
and the results are returned across the Row of each vertical criterion.

Sample Layout Results Table:
Column A Row4 to many Rows holds consecutive Crtieria eg: 60-61
Column E to across many Columns will hold the calculated individual Intervals
for each consecutive criteria pair:

Col A Row4 (2 consecutive criteria per Row) 60-61 Col E Row4 Return count of
Intervals across Row
Col A Row5 (2 consecutive criteria per Row) 61-62 Col E Row5 Return count of
Intervals across Row


Thanks
Sam

 

[Domenic]

First, define the following reference...

Select E4

Insert > Name > Define

Name: Array

Refers to:

=MMULT(((Results+0)=LEFT(Sheet1!$A4,2)+0)+((Results+0)=RIGHT(Sheet1!$A4,2
)+0),TRANSPOSE(COLUMN(Results)^0))

Click Ok

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

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

....confirmed with CONTROL+SHIFT+ENTER. Change the sheet reference
accordingly. Also, if you format Column A and 'Results' as text, you
can eliminate the '+0' bit from the defined reference, 'Array'.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for reply. I'm receiving #Value error - not sure Why?

Cheers
Sam

First, define the following reference...

Select E4

Insert > Name > Define

Name: Array

Refers to:

=MMULT(((Results+0)=LEFT(Sheet1!$A4,2)+0)+((Results+0)=RIGHT(Sheet1!$A4,2
)+0),TRANSPOSE(COLUMN(Results)^0))

Click Ok

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

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

...confirmed with CONTROL+SHIFT+ENTER. Change the sheet reference
accordingly. Also, if you format Column A and 'Results' as text, you
can eliminate the '+0' bit from the defined reference, 'Array'.

Hope this helps!

Hi All,

[quoted text clipped - 34 lines]

Thanks
Sam

 

[Domenic]

Just to be clear...

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

2) Can you provide a sample of the value/criteria contained in A4?

3) Can you confirm whether Column A and 'Results' are formatted as
'Text' or 'General'?

 

[Sam via OfficeKB.com]

Hi Domenic,

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

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

2) Can you provide a sample of the value/criteria contained in A4?

01-02 (Sheet2)

3) Can you confirm whether Column A and 'Results' are formatted as 'Text' or 'General'

Both General

Cheers,
Sam

 

[Domenic]

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

09-18-36-46-56-66 (Sheet1)[/QUOTE]

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(COLUMN(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?

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much - that's Brilliant!