Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Return Consecutive Values - Pairs
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 ------------------------------------------------------------------------ =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) ,""),""),"") -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
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 -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
In article ,
"Sam via OfficeKB.com" wrote: 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(MAT CH(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! |
#6
|
|||
|
|||
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(MAT CH(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. -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
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. :) In article , "Sam via OfficeKB.com" wrote: Hi Domenic, Thank you for further input. Great alternative solution! Thanks Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Return Consecutive Values | Excel Worksheet Functions | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
How to use an array or matrix to return text vs. numeric values | Excel Worksheet Functions | |||
show a set of diagonal values in a matrix as a set of consecutive. | Excel Worksheet Functions |