Return Consecutive Values - Pairs

[Sam via OfficeKB.com]

Hi All,

In June 2005, Domenic, very kindly created a working Formula for my
Consecutive Value scenario - original thread listed below:-
http://www.officekb.com/Uwe/[email protected]


Consecutive values to be returned to Sheet2 by using
the input values on Row 1 of Sheet2. All consecutive values
returned by individual pairs. There are instances where a Row on Sheet1 may
contain consecutive values as below, and what I actually wish
to see is all instances for example of 82-83, 83-84, 84-85, 85-86, 86-87
and 87-88.

Sheet1!B19:H19 contains...
82 83 84 85 86 87 88

Sheet2!B1:K1 contains..
80 81 82 83 84 85 86 87 88 89

1) Each row in the source table (Sheet1) contains unique values.

2) Each row in the source table contains values in ascending order.

Formula by Domenic - Sheet2:
B19, copied down and across to J19:

=IF(ISNUMBER(MATCH(B$1,Sheet1!$B19:$H19,0)),IF(MATCH(B$1,Sheet1!$B19:$H19
,0)<7,IF(INDEX(Sheet1!$B19:$H19,MATCH(B$1,Sheet1!$B19:$H19,0)+1)=B$1+1,B$
1&"-"&B$1+1,""),""),"")

The above Formula works great!

However, if possible, I would like an adaptation of the above Formula to work
with the following scenario:

1) Each row in the source table (Sheet1) contains unique values.

2) Each row in the source table contains values in ascending order.

3) Input cells - I still require the consecutive values to be returned to
Sheet2 by using the input values on Row 1 of Sheet2 BUT I would like the
Input values on Sheet2 to be referenced as 80-81 in a single cell, 81-82 in
the next cell, 82-83 in next cell etc. I'm not sure but I think EXCEL may see
the numbers separated by the hyphen as TEXT?

Sheet1!B19:H19 contains...
82 83 84 85 86 87 88

Sheet2!B1:K1 contains..
80-81 81-82 82-83 83-84 84-85 85-86 86-87 87-88 88-89 89-90

I've tried to manipulate Domenic's original working Formula to provide a
solution to my new scenario - well.... had to give it a try, but to no avail!

Manipulated non-working Formula:
=IF(ISTEXT(MATCH(--LEFT(B$1,1),Sheet1!$B19:$H19,0)),IF(MATCH(--LEFT(B$1,1),
Sheet1!$B19:$H19,0)<7,IF(INDEX(Sheet1!$B19:$H19,MATCH(--LEFT(B$1,1),Sheet1!
$B19:$H19,0)+1)+--LEFT(B$1,1)=--LEFT(B$1,1)+1,--LEFT(B$1,1)+1,""),""),"")

Any help most appreciated.

Thanks
Sam

 

[Domenic]

If your 'Input' values will always be two digits, try...

=IF(ISNUMBER(MATCH(LEFT(B$1,2)+0,Sheet1!$B19:$H19,0)),IF(MATCH(LEFT(B$1,2
)+0,Sheet1!$B19:$H19,0)<7,IF(INDEX(Sheet1!$B19:$H19,MATCH(LEFT(B$1,2)+0,S
heet1!$B19:$H19,0)+1)=(LEFT(B$1,2)+1),(LEFT(B$1,2)+0)&"-"&(LEFT(B$1,2)+1)
,""),""),"")

If this is not the case, post back.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you very much for assisting with your Formula below, I just tweaked the
nested LEFT function to LEFT(B$1,1) rather than LEFT(B$1,2); otherwise
perfect.

Thank you, once again.
Sam

 

[Sam via OfficeKB.com]

Hi Domenic,

I forget to say why I tweaked nested LEFT Function in Formula - there are a
couple of Input cells with single digits, so they were tweaked with LEFT(B$1,
1). The Formula remains as your Posting for all double digit values. Based on
the values shown in my Posting your Formula was spot on!

Thanks
Sam

 

[Domenic]

I forget to say why I tweaked nested LEFT Function in Formula - there are a
couple of Input cells with single digits, so they were tweaked with LEFT(B$1,
1). The Formula remains as your Posting for all double digit values. Based on
the values shown in my Posting your Formula was spot on!

An alternative might be as follows...

1) enter your Input cells as you would normally in Row 1

2) extract the relevant number (single or double digit) in the second
row...

B2, copied across:

=LEFT(B1,FIND("-",B1)-1)+0

3) have the formula refer to this row...

B19, copied across:

=IF(ISNUMBER(MATCH(B$2,Sheet1!$B19:$H19,0)),IF(MATCH(B$2,Sheet1!$B19:$H19
,0)<7,IF(INDEX(Sheet1!$B19:$H19,MATCH(B$2,Sheet1!$B19:$H19,0)+1)=B$2+1,B$
2&"-"&B$2+1,""),""),"")

This way you could dispense with having to alter the LEFT function,
depending on your data. And, if you wish, you can hide the second row
by either hiding the row itself or using conditional formatting and
choosing 'White' as your font color.

Hope this helps!

 

[Sam via OfficeKB.com]

Hi Domenic,

Thank you for further input. Great alternative solution!

Thanks
Sam

---------------------------------------------------------------------

An alternative might be as follows...

1) enter your Input cells as you would normally in Row 1

2) extract the relevant number (single or double digit) in the second
row...

B2, copied across:

=LEFT(B1,FIND("-",B1)-1)+0

3) have the formula refer to this row...

B19, copied across:

=IF(ISNUMBER(MATCH(B$2,Sheet1!$B19:$H19,0)),IF(MATCH(B$2,Sheet1!$B19:$H19
,0)<7,IF(INDEX(Sheet1!$B19:$H19,MATCH(B$2,Sheet1!$B19:$H19,0)+1)=B$2+1,B$
2&"-"&B$2+1,""),""),"")

This way you could dispense with having to alter the LEFT function,
depending on your data. And, if you wish, you can hide the second row
by either hiding the row itself or using conditional formatting and
choosing 'White' as your font color.

 

[Domenic]

Just a quick correction. You don't need to use 'Conditional Formatting'
to hide the values for your second row. Just select 'White' as your
font color, which I'm sure you've already figured out. Not sure why I
mentioned conditional formatting in the first place.