|
Re: Culling consecutive numbers into ranges
Dawned on me somewhat belatedly that you might want to do this with formulae ?
If so... if you ensure there is a cell above the first number ... ie:
Code:
Range: A2:A11
2759
2760
2761
2762
2764
2765
2766
2768
2769
2773
You could in theory return the blocks in B such that:
Code:
B1:
=SUMPRODUCT(--(A2:A11>(A1:A10+1)))
(gives count of "blocks")
B2:
=IF(ROWS(B$2:B2)>$B$1,"",SMALL(IF(($A$2:$A$11-$A$1:$A$10)>1,$A$2:$A$11),ROWS(B$2:B2))&LOOKUP(REPT("Z",255),CHOOSE({1,2},"","-"&SMALL(IF(($A$3:$A$12-$A$2:$A$11)>1,$A$2:$A$11),ROWS(B$2:B2)))))
committed with CTRL + SHIFT + ENTER
copied down to say B11
As you alter the values in A2:A11 so you should find the blocks update - obviously based on the premise that the values in A2:A11 are listed in Ascending order and that you are concerned with a step of > 1.
|