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:$H1 9,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!
In article ,
"Sam via OfficeKB.com" wrote:
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...urn-Consecutiv
e-Values#dc2020851e1e4b6480c54911ff5f8348%40OfficeKB .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