Sort data according to largest groupings of consecutive numbers

[lwhyatt]

Is it possible to sort a column of data into groups of conecutive numbers and then sort said groups into descending size order?

eg
1
2
3
4 (4 consecutive)
99
101
102
103
104
105
106
107 (7 consecutive)
200
210
211
212
213
214
215
216
217 (8 consecutive)

becomes...

210
211
212
213
214
215
216
217

101
102
103
104
105
106
107

1
2
3
4

99
200

 

[lwhyatt]

sorry should read Is it possible to sort a column of data into groups of consecutive numbers and then sort said groups into descending size order?

 

[lwhyatt]

So this tests if numbers are consecutive:

=IF(A2-A1=1,"C",0)

Trouble is, it only highlights the second entry in a consecutive list ( no C at the start of the run).

I then need to sort column A starting with those that have the largest blocks of C's down to the smalest (1's)

 

[lwhyatt]

So this tests if numbers are consecutive:

=IF(A2-A1=1,"C",0)

View attachment 80038

Trouble is, it only highlights the second entry in a consecutive list ( no C at the start of the run).

I then need to sort column A starting with those that have the largest blocks of C's down to the smalest (1's)

This then corrects it

=IF(A3-A2=1,"C",IF(A1-A2=-1,"C",0))

But it also makes out there's bigger blocks if consecutive groups ajoin

eg

333 C
334 C
335 C
1234C
1235C
1236C

makes it look like 6 consecutive numbers when its two groups of three!

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)