Culling consecutive numbers into ranges

[comicbook]

I have a spreadsheet that has a column of numbers some of which are consecutive, some of which are not. I would like to have a way to lump all of these chunks of consecutive blocks into ranges. For example:

2759
2760
2761
2762
2764
2765
2766
2768
2769
2773

would return something like:

2759 - 2762
2764 - 2766
2768 - 2769
2773

Any ideas?

Help would be much appreciated.

[DonkeyOte]

Quite a few ways to do this, looping etc... not sure how much data you have and/or where you want your resulting values to appear... below is based on replacing original values in A with ranged numbers...

Please Login or Register  to view this content.

[DonkeyOte]

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:

Please Login or Register  to view this content.

You could in theory return the blocks in B such that:

Please Login or Register  to view this content.

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.

[comicbook]

Thank you for the quick response.

I tried both the vb script and the formula option.

The vb script gave me a runtime 6: overflow error.

I'm not sure, but it could be because my data set is in the thousands. Although I tried it with a subset of about 20 numbers and had the same failure.

I then tried the formula version and I'm not sure exactly what it did (or was supposed to do) It seemed to fill column B with numbers, but they were not they were not really ranges. I'm not sure how to explain it. Perhaps you could explain what the output should look like.

Thank you very much for your help. I really appreciate it.

[DonkeyOte]

How big (rather than how many) are your numbers ?

If the values > Longs boundaries (–2,147,483,648 to 2,147,486,647) you could perhaps alter variables from Long to Currency.

Provide the subset of 20 numbers you mention.

[Krishnakumar]

Hi,

Please Login or Register  to view this content.

Note: Code edited.


HTH

[comicbook]

My numbers are quite large. Here is the subset that I tested with:

8634767317
8634767318
8634767319
8634767320
8634767321
8634767323
8634767325
8634767326
8634767334
8634767339
8634767341
8634767344
8634767345
8634767347
8634767348
8634767349
8634767350
8634767352
8634767353
8634767354

[comicbook]

Kris,

Your script worked perfectly.

It gave the exact output I was looking for.

Thanks much for the effort.

Thanks also to DonkeyOte for working on this.

This is an immensely helpful forum

[DonkeyOte]

Kris' code didn't work for me based on the sample 20 as it needs (I think) a slight tweak re: last range.

Anyway - below is revision of my original using Currency as opposed to Long as outlined previously and a couple of other tweaks as it didn't work correctly anyway.

Please Login or Register  to view this content.

[Magnus_CA]

Kris' code didn't work for me based on the sample 20 as it needs (I think) a slight tweak re: last range.

Anyway - below is revision of my original using Currency as opposed to Long as outlined previously and a couple of other tweaks as it didn't work correctly anyway.

Please Login or Register  to view this content.

DonkeyOte,

I'm getting a type mismatch vb error when running this sub on Excel 2000. My data is in column A. Here's some sample data:

74
76
92
94
96
106
144
145
146
147
148
163
165
166
168
...

The values ascend to 62000. My hope was that your sub would return values in column B like:

74-74
76-76
92-92
94-94
96-96
106-106
144-148
163-163
165-166
168-...

Can you help me?

Thanks in advance.

[arlu1201]

Magnus,

This thread is 4 yrs old so its better you create a thread of your own.