Return Consecutive Values

I haven't fully tested it, but see if this will do...

Assuming that Sheet1 contains your source data, and Sheet2 contains your
expected results...

1) If there will always be at least one set of consecutive pairs...

Sheet2!B19, copied across and down:

=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1
)))=B$1,SUM(B$1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),""))

....confirmed with CONTROL+SHIFT+ENTER.

2) If there may not be at least one consecutive pair...

Sheet2!B19, copied across and down:

=IF(1-ISNA(MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1))),IF(INDEX(She
et1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1)))=B$1,SUM(B
$1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),"")),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi All,
>
> I am using the formula below to return one specific value referenced in an
> input cell B1:
>
> =IF((COUNTIF(INDEX(Stock,ROW()-ROW(Stock)+1,0),B$1))=1,B$1,"")
>
> How might a more suitable formula or the above formula be expanded to find
> pairs of consecutive values in ascending order in a Dynamic Defined Range
> “Stock” ( 7 Columns B-H and many Rows), and return the two consecutive
> values on the same Row in separate columns (side by side) on a new sheet.
>
> Sample Data – Defined Dynamic Range “Stock”:
> COL B C D E F G H
> ROW19 70 72 87 88 89 92 95
> ROW20 73 74 78 80 81 83 84
>
> In case data is misaligned: Row 19, Column B, C, D, E, F, G, and H houses
> 70, 72, 87, 88, 89, 92 and 95 respectively. Row 20, Column C, D, E, F, G,
> and H houses 73 74, 78, 80, 81, 83 and 84 respectively.
>
> New Worksheet (for Expected Results)
> ROW1 Input cells: B$1, C$1, D$1, E$1, F$1, G$1, H$1, I$1, J$1, K$1
> 80 81 82 83 84 85 86 87 88 89
>
> In case data is misaligned: cell B1, C1, D1, E1, F1, G1, H1, I1, J1 and K1
> house 80, 81, 82, 83, 84, 85, 86, 87, 88 and 89
>
> Expected Results:
> COL B C D E F G H I J K
> ROW19 88 89
> ROW20 83 84
>
> Row19 – 88 and 89 returned in columns J and K
> Row20 – 83 and 84 returned in columns E and F
>
> Kind Regards,
> Sam

Hi Domenic,

Thank you very much for all your help. The formula works great - brilliant!


Thanks
Sam

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

Assumptions:

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

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

Formula:

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,""),""),"")

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> Thank you very much for sample file. Your interpretation of what I said is
> perfect and the file does what I requested. However, as usual, I did not
> fully understand what I actually wanted to see visually on the new
> worksheet - Sheet2.
>
> I still require the consecutive values to be returned to Sheet2 by using
> the input values on Row 1 of Sheet2 but I need all consecutive values
> returned by individual pairs and not by individual numbers. Using the
> current formula I would get 82-83-84-85-86-87-88. There are instances where
> a Row may contain all 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. I'm not sure whether having two columns for each input value
> would help.
>
> Is there a way to achieve the above?
>
> 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
>
>
> Thanks
> Sam

Hi Domenic,

Thank you very much for sample file. Your interpretation of what I said is
perfect and the file does what I requested. However, as usual, I did not
fully understand what I actually wanted to see visually on the new
worksheet - Sheet2.

I still require the consecutive values to be returned to Sheet2 by using
the input values on Row 1 of Sheet2 but I need all consecutive values
returned by individual pairs and not by individual numbers. Using the
current formula I would get 82-83-84-85-86-87-88. There are instances where
a Row may contain all 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. I'm not sure whether having two columns for each input value
would help.

Is there a way to achieve the above?

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


Thanks
Sam

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

Hi Sam,

I've just emailed you my sample file. Let me know how it works out.

Cheers!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> I would appreciate having a look at your sample file.
> Email sam_518 AT hotmail DOT co DOT uk
>
> Thanks
> Sam

Hi Domenic,

I would appreciate having a look at your sample file.
Email sam_518 AT hotmail DOT co DOT uk

Thanks
Sam

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

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> However, I'm not sure why it is
> not returning all the consecutive values on the 2nd Sheet for the first two
> columns. I should see 9 values in columns "B" and "C"
> that are consecutive.

Would you like me to take a look at a sample of your file and see if I
can pinpoint the problem? If so, you can email me at
[email protected]. Otherwise, I can email you a sample file.

Hi Domenic,

Yes, those are the results I'm looking for. However, I'm not sure why it is
not returning all the consecutive values on the 2nd Sheet for the first two
columns. I should see 9 values in columns "B" and "C"
that are consecutive.

Also as suggested, would appreciate it if you could eliminate the N/A
values by modyfying the formula.

Thanks
Sam

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

Hi Sam!

Maybe I've misunderstood what it is you're looking for. Let's take a
look at the following example...

Sheet1!B19:H24 contains...

88 89 90 91 92 93 94
75 76 77 78 79 80 81
70 78 79 84 85 90 92
75 80 81 85 87 88 92
74 75 76 77 78 79 80
89 90 91 92 93 94 95

Sheet2!B1:K1 contains..

80 81 82 83 84 85 86 87 88 89

Sheet2!B19:K24 would contain the following results...

0 0 0 0 0 0 0 0 88 89
80 81 0 0 0 0 0 0 0 0
0 0 0 0 84 85 0 0 0 0
0 0 0 0 0 0 0 87 88 0
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

Note that the 0's are only there to help differentiate the columns, and
the #N/A values can be dealt with by either hiding them using
Conditional Formatting or modifying the formula to eliminate them. The
#N/A values are returned when there are no consecutive pairs or, if they
exist, don't fall within the range specified by the input cells.

Now, are these the results your looking for? If not, can you supply the
correct results?

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic & Biff,
>
> I've implemented the formula below across the first two columns ("B" and
> "C") in Sheet2 but it produces the N/A error on some rows and
> unfortunately, still only returns the same 4 consecutive values that the
> previous formula returned. I should see 9 values in columns "B" and "C"
> that are consecutive.
>
> Column "A" is empty and remembered to enter with CONTROL+SHIFT+ENTER.
>
> Any further ideas and help much appreciated.
>
> Thanks
> Sam
> -----------------------------------------------------------------------
>
> Here's my second attempt at a solution. This time I hope I've covered
> all the bases. I guess we'll see...
>
> Sheet2!B19, copied across and down:
>
> =IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
> 1)*(Sheet1!$B19:$G19>=80)*(Sheet1!$C19:$H19<=89))))=B$1,SUM(B$1,A19),IF(C
> OUNTIF($A19:A19,">0")=1,SUM(A19,1),""))
>
> ...confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
> applies here. Column A, starting at A19, must either be blank or
> contain text values.

Hi Domenic & Biff,

I've implemented the formula below across the first two columns ("B" and
"C") in Sheet2 but it produces the N/A error on some rows and
unfortunately, still only returns the same 4 consecutive values that the
previous formula returned. I should see 9 values in columns "B" and "C"
that are consecutive.

Column "A" is empty and remembered to enter with CONTROL+SHIFT+ENTER.

Any further ideas and help much appreciated.

Thanks
Sam
-----------------------------------------------------------------------

Here's my second attempt at a solution. This time I hope I've covered
all the bases. I guess we'll see...

Sheet2!B19, copied across and down:

=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
1)*(Sheet1!$B19:$G19>=80)*(Sheet1!$C19:$H19<=89))))=B$1,SUM(B$1,A19),IF(C
OUNTIF($A19:A19,">0")=1,SUM(A19,1),""))

....confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
applies here. Column A, starting at A19, must either be blank or
contain text values.

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

Hi Domenic,

Thank you for assistance and ongoing help.

I'm using your suggested 2nd option below - the formula did not return all
the instances of expected consecutive values. I've just picked up on Biff's
posting and your amended version - so I'll post back shortly.

Thanks
Sam
-------------------------------------------------------------------------

2) If there may not be at least one consecutive pair...
Sheet2!B19, copied across and down:

=IF(1-ISNA(MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1))),IF(INDEX(She
et1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1)))=B$1,SUM(B
$1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),"")),"")

....confirmed with CONTROL+SHIFT+ENTER.

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

Hi Biff!

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> Change the values in Sheet1 row 19 to:
>
> 85:91
>
> Sheet2 row 19 = ""

Thanks for catching it! Much appreciated!

> This is a real challenge!

Most definitely! And I do like a challenge!

Here's my second attempt at a solution. This time I hope I've covered
all the bases. I guess we'll see...

Sheet2!B19, copied across and down:

=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
1)*(Sheet1!$B19:$G19>=80)*(Sheet1!$C19:$H19<=89))))=B$1,SUM(B$1,A19),IF(C
OUNTIF($A19:A19,">0")=1,SUM(A19,1),""))

....confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
applies here. Column A, starting at A19, must either be blank or
contain text values.

Hope this helps!

Hi Domenic!

Change the values in Sheet1 row 19 to:

85:91

Sheet2 row 19 = ""

This is a real challenge!

Biff

"Domenic" <[email protected]> wrote in message
news:[email protected]...
>I haven't fully tested it, but see if this will do...
>
> Assuming that Sheet1 contains your source data, and Sheet2 contains your
> expected results...
>
> 1) If there will always be at least one set of consecutive pairs...
>
> Sheet2!B19, copied across and down:
>
> =IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1
> )))=B$1,SUM(B$1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),""))
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> 2) If there may not be at least one consecutive pair...
>
> Sheet2!B19, copied across and down:
>
> =IF(1-ISNA(MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1))),IF(INDEX(She
> et1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1)))=B$1,SUM(B
> $1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),"")),"")
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Sam via OfficeKB.com" <[email protected]> wrote:
>
>> Hi All,
>>
>> I am using the formula below to return one specific value referenced in
>> an
>> input cell B1:
>>
>> =IF((COUNTIF(INDEX(Stock,ROW()-ROW(Stock)+1,0),B$1))=1,B$1,"")
>>
>> How might a more suitable formula or the above formula be expanded to
>> find
>> pairs of consecutive values in ascending order in a Dynamic Defined Range
>> "Stock" ( 7 Columns B-H and many Rows), and return the two consecutive
>> values on the same Row in separate columns (side by side) on a new sheet.
>>
>> Sample Data - Defined Dynamic Range "Stock":
>> COL B C D E F G H
>> ROW19 70 72 87 88 89 92 95
>> ROW20 73 74 78 80 81 83 84
>>
>> In case data is misaligned: Row 19, Column B, C, D, E, F, G, and H
>> houses
>> 70, 72, 87, 88, 89, 92 and 95 respectively. Row 20, Column C, D, E, F,
>> G,
>> and H houses 73 74, 78, 80, 81, 83 and 84 respectively.
>>
>> New Worksheet (for Expected Results)
>> ROW1 Input cells: B$1, C$1, D$1, E$1, F$1, G$1, H$1, I$1, J$1, K$1
>> 80 81 82 83 84 85 86 87 88 89
>>
>> In case data is misaligned: cell B1, C1, D1, E1, F1, G1, H1, I1, J1 and
>> K1
>> house 80, 81, 82, 83, 84, 85, 86, 87, 88 and 89
>>
>> Expected Results:
>> COL B C D E F G H I J K
>> ROW19 88 89
>> ROW20 83 84
>>
>> Row19 - 88 and 89 returned in columns J and K
>> Row20 - 83 and 84 returned in columns E and F
>>
>> Kind Regards,
>> Sam

I forgot to mention that this will only work if Column A on Sheet2
(starting at A19 onwards) is either blank or contains text values.

Hope this helps!

In article <[email protected]>,
Domenic <[email protected]> wrote:

> I haven't fully tested it, but see if this will do...
>
> Assuming that Sheet1 contains your source data, and Sheet2 contains your
> expected results...
>
> 1) If there will always be at least one set of consecutive pairs...
>
> Sheet2!B19, copied across and down:
>
> =IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1
> )))=B$1,SUM(B$1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),""))
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> 2) If there may not be at least one consecutive pair...
>
> Sheet2!B19, copied across and down:
>
> =IF(1-ISNA(MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1))),IF(INDEX(She
> et1!$B19:$G19,MATCH(2,1/(Sheet1!$C19:$H19-Sheet1!$B19:$G19=1)))=B$1,SUM(B
> $1,A19),IF(COUNTIF($A19:A19,">0")=1,SUM(A19,1),"")),"")
>
> ...confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Sam via OfficeKB.com" <[email protected]> wrote:
>
> > Hi All,
> >
> > I am using the formula below to return one specific value referenced in an
> > input cell B1:
> >
> > =IF((COUNTIF(INDEX(Stock,ROW()-ROW(Stock)+1,0),B$1))=1,B$1,"")
> >
> > How might a more suitable formula or the above formula be expanded to find
> > pairs of consecutive values in ascending order in a Dynamic Defined Range
> > “Stock” ( 7 Columns B-H and many Rows), and return the two consecutive
> > values on the same Row in separate columns (side by side) on a new sheet.
> >
> > Sample Data – Defined Dynamic Range “Stock”:
> > COL B C D E F G H
> > ROW19 70 72 87 88 89 92 95
> > ROW20 73 74 78 80 81 83 84
> >
> > In case data is misaligned: Row 19, Column B, C, D, E, F, G, and H houses
> > 70, 72, 87, 88, 89, 92 and 95 respectively. Row 20, Column C, D, E, F, G,
> > and H houses 73 74, 78, 80, 81, 83 and 84 respectively.
> >
> > New Worksheet (for Expected Results)
> > ROW1 Input cells: B$1, C$1, D$1, E$1, F$1, G$1, H$1, I$1, J$1, K$1
> > 80 81 82 83 84 85 86 87 88 89
> >
> > In case data is misaligned: cell B1, C1, D1, E1, F1, G1, H1, I1, J1 and K1
> > house 80, 81, 82, 83, 84, 85, 86, 87, 88 and 89
> >
> > Expected Results:
> > COL B C D E F G H I J K
> > ROW19 88 89
> > ROW20 83 84
> >
> > Row19 – 88 and 89 returned in columns J and K
> > Row20 – 83 and 84 returned in columns E and F
> >
> > Kind Regards,
> > Sam

Hi All,

I am using the formula below to return one specific value referenced in an
input cell B1:

=IF((COUNTIF(INDEX(Stock,ROW()-ROW(Stock)+1,0),B$1))=1,B$1,"")

How might a more suitable formula or the above formula be expanded to find
pairs of consecutive values in ascending order in a Dynamic Defined Range
“Stock” ( 7 Columns B-H and many Rows), and return the two consecutive
values on the same Row in separate columns (side by side) on a new sheet.

Sample Data – Defined Dynamic Range “Stock”:
COL B C D E F G H
ROW19 70 72 87 88 89 92 95
ROW20 73 74 78 80 81 83 84

In case data is misaligned: Row 19, Column B, C, D, E, F, G, and H houses
70, 72, 87, 88, 89, 92 and 95 respectively. Row 20, Column C, D, E, F, G,
and H houses 73 74, 78, 80, 81, 83 and 84 respectively.

New Worksheet (for Expected Results)
ROW1 Input cells: B$1, C$1, D$1, E$1, F$1, G$1, H$1, I$1, J$1, K$1
80 81 82 83 84 85 86 87 88 89

In case data is misaligned: cell B1, C1, D1, E1, F1, G1, H1, I1, J1 and K1
house 80, 81, 82, 83, 84, 85, 86, 87, 88 and 89

Expected Results:
COL B C D E F G H I J K
ROW19 88 89
ROW20 83 84

Row19 – 88 and 89 returned in columns J and K
Row20 – 83 and 84 returned in columns E and F

Kind Regards,
Sam

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

Hi Domenic,

Thank you very much for sample file. Your interpretation of what I said is
perfect and the file does what I requested. However, as usual, I did not
fully understand what I actually wanted to see visually on the new
worksheet - Sheet2.

I still require the consecutive values to be returned to Sheet2 by using
the input values on Row 1 of Sheet2 but I need all consecutive values
returned by individual pairs and not by individual numbers. Using the
current formula I would get 82-83-84-85-86-87-88. There are instances where
a Row may contain all 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. I'm not sure whether having two columns for each input value
would help.

Is there a way to achieve the above?

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


Thanks
Sam

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

Assumptions:

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

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

Formula:

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,""),""),"")

Hope this helps!

In article <[email protected]>,
"Sam via OfficeKB.com" <[email protected]> wrote:

> Hi Domenic,
>
> Thank you very much for sample file. Your interpretation of what I said is
> perfect and the file does what I requested. However, as usual, I did not
> fully understand what I actually wanted to see visually on the new
> worksheet - Sheet2.
>
> I still require the consecutive values to be returned to Sheet2 by using
> the input values on Row 1 of Sheet2 but I need all consecutive values
> returned by individual pairs and not by individual numbers. Using the
> current formula I would get 82-83-84-85-86-87-88. There are instances where
> a Row may contain all 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. I'm not sure whether having two columns for each input value
> would help.
>
> Is there a way to achieve the above?
>
> 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
>
>
> Thanks
> Sam

Hi Domenic,

Thank you very much for all your help. The formula works great - brilliant!


Thanks
Sam

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