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/Forum.as...OfficeKB .com


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(MAT CH(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,MA TCH(--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

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