Tags: column, excel, grouping, groups, header, headers, highest, lists, lowest, microsoft, msdn, numbers, separate, sequential, software, sorts, sub

Grouping Sequential Numbers

On Microsoft » Microsoft Excel

9,704 words with 3 Comments; publish: Fri, 23 May 2008 04:34:00 GMT; (30677.88, « »)

I have a sub that groups lists of numbers under their headers (each header in

a separate column) and then sorts each column from lowest to highest. Some

columns have 60,000+ values so it is a fairly large group of data. Most of

these numbers are sequential, but there are places where the sequense skips a

few. What I'm trying to do is group all the sequential numbers into a text

range thereby deceasing the number of rows used in each column. The code

below does the job, but because it loops through each line, it takes a long

time to complete. I'm wondering if there is a faster method of doing this.

Sample data (before code):

1

2

3

4

5

9

10

11

13

18

19

20

Sample data (after code) - Note: the range lists are TEXT strings now, not

numbers:

1 to 5

9 to 11

13

18 to 20

Code is as follows:

Do While rng.Column <= Sheet2.UsedRange.Columns.Count

i = 1

j = 0

start = rng.Value

nextln = rng.Offset(1, 0).Value

Do While Not nextln = ""

If nextln - start = i Then

i = i + 1

rng.Value = start & " to " & nextln

Cells(rng.Row + 1, rng.Column).Delete (xlShiftUp)

Else *****SEE NOTE BELOW ******

Set rng = rng.Offset(1, 0)

i = 1

start = rng.Value

End If

nextln = rng.Offset(1, 0).Value

Loop

Set rng = Sheet2.Cells(2, rng.Column + 1)

Loop

Any help or suggestion is greatly appreciated!

Thanks,

Mike

All Comments

Leave a comment...

  • 3 Comments
    • Without seeing your data and such it is a little hard to comment. the only

      suggestions that I might have to speed things up would be to turn calculation

      off at the beginning and on again at the end. Other than that you are

      deleting essentially one record at a time which is slow. Try to create a

      large range (union is a number of small ranges) encompassing all of the data

      you want to delete and then just perform one big delete at the end.

      --

      HTH...

      Jim Thomlinson

      "crazybass2" wrote:

      > I have a sub that groups lists of numbers under their headers (each header in

      > a separate column) and then sorts each column from lowest to highest. Some

      > columns have 60,000+ values so it is a fairly large group of data. Most of

      > these numbers are sequential, but there are places where the sequense skips a

      > few. What I'm trying to do is group all the sequential numbers into a text

      > range thereby deceasing the number of rows used in each column. The code

      > below does the job, but because it loops through each line, it takes a long

      > time to complete. I'm wondering if there is a faster method of doing this.

      > Sample data (before code):

      > 1

      > 2

      > 3

      > 4

      > 5

      > 9

      > 10

      > 11

      > 13

      > 18

      > 19

      > 20

      > Sample data (after code) - Note: the range lists are TEXT strings now, not

      > numbers:

      > 1 to 5

      > 9 to 11

      > 13

      > 18 to 20

      >

      > Code is as follows:

      > Do While rng.Column <= Sheet2.UsedRange.Columns.Count

      > i = 1

      > j = 0

      > start = rng.Value

      > nextln = rng.Offset(1, 0).Value

      > Do While Not nextln = ""

      > If nextln - start = i Then

      > i = i + 1

      > rng.Value = start & " to " & nextln

      > Cells(rng.Row + 1, rng.Column).Delete (xlShiftUp)

      > Else *****SEE NOTE BELOW ******

      > Set rng = rng.Offset(1, 0)

      > i = 1

      > start = rng.Value

      > End If

      > nextln = rng.Offset(1, 0).Value

      > Loop

      > Set rng = Sheet2.Cells(2, rng.Column + 1)

      > Loop

      >

      > Any help or suggestion is greatly appreciated!

      > Thanks,

      > Mike

      #1; Fri, 23 May 2008 04:36:00 GMT
    • The process you are trying to do requires, regardless of methodology, that

      each cell in a column be evaluated. If you are using almost every row, as

      you mention, this will take a very very long time.

      Can you perhaps explains why you are trying to do this... people may have

      some creative ideas to reach you ultimate end-state.

      "Jim Thomlinson" wrote:

      > Without seeing your data and such it is a little hard to comment. the only

      > suggestions that I might have to speed things up would be to turn calculation

      > off at the beginning and on again at the end. Other than that you are

      > deleting essentially one record at a time which is slow. Try to create a

      > large range (union is a number of small ranges) encompassing all of the data

      > you want to delete and then just perform one big delete at the end.

      > --

      > HTH...

      > Jim Thomlinson

      >

      > "crazybass2" wrote:

      > > I have a sub that groups lists of numbers under their headers (each header in

      > > a separate column) and then sorts each column from lowest to highest. Some

      > > columns have 60,000+ values so it is a fairly large group of data. Most of

      > > these numbers are sequential, but there are places where the sequense skips a

      > > few. What I'm trying to do is group all the sequential numbers into a text

      > > range thereby deceasing the number of rows used in each column. The code

      > > below does the job, but because it loops through each line, it takes a long

      > > time to complete. I'm wondering if there is a faster method of doing this.

      > >

      > > Sample data (before code):

      > >

      > > 1

      > > 2

      > > 3

      > > 4

      > > 5

      > > 9

      > > 10

      > > 11

      > > 13

      > > 18

      > > 19

      > > 20

      > >

      > > Sample data (after code) - Note: the range lists are TEXT strings now, not

      > > numbers:

      > >

      > > 1 to 5

      > > 9 to 11

      > > 13

      > > 18 to 20

      > >

      > >

      > > Code is as follows:

      > >

      > > Do While rng.Column <= Sheet2.UsedRange.Columns.Count

      > > i = 1

      > > j = 0

      > > start = rng.Value

      > > nextln = rng.Offset(1, 0).Value

      > > Do While Not nextln = ""

      > > If nextln - start = i Then

      > > i = i + 1

      > > rng.Value = start & " to " & nextln

      > > Cells(rng.Row + 1, rng.Column).Delete (xlShiftUp)

      > > Else *****SEE NOTE BELOW ******

      > > Set rng = rng.Offset(1, 0)

      > > i = 1

      > > start = rng.Value

      > > End If

      > > nextln = rng.Offset(1, 0).Value

      > > Loop

      > > Set rng = Sheet2.Cells(2, rng.Column + 1)

      > > Loop

      > >

      > >

      > > Any help or suggestion is greatly appreciated!

      > >

      > > Thanks,

      > > Mike

      #2; Fri, 23 May 2008 04:37:00 GMT
    • Jim,

      Thanks for the reply. I do have both ScreenUpdating and Calculations off

      throughout the entire code. I will try as you suggested to use the Union or

      similar type function.

      Mike

      "Jim Thomlinson" wrote:

      > Without seeing your data and such it is a little hard to comment. the only

      > suggestions that I might have to speed things up would be to turn calculation

      > off at the beginning and on again at the end. Other than that you are

      > deleting essentially one record at a time which is slow. Try to create a

      > large range (union is a number of small ranges) encompassing all of the data

      > you want to delete and then just perform one big delete at the end.

      > --

      > HTH...

      > Jim Thomlinson

      >

      > "crazybass2" wrote:

      > > I have a sub that groups lists of numbers under their headers (each header in

      > > a separate column) and then sorts each column from lowest to highest. Some

      > > columns have 60,000+ values so it is a fairly large group of data. Most of

      > > these numbers are sequential, but there are places where the sequense skips a

      > > few. What I'm trying to do is group all the sequential numbers into a text

      > > range thereby deceasing the number of rows used in each column. The code

      > > below does the job, but because it loops through each line, it takes a long

      > > time to complete. I'm wondering if there is a faster method of doing this.

      > >

      > > Sample data (before code):

      > >

      > > 1

      > > 2

      > > 3

      > > 4

      > > 5

      > > 9

      > > 10

      > > 11

      > > 13

      > > 18

      > > 19

      > > 20

      > >

      > > Sample data (after code) - Note: the range lists are TEXT strings now, not

      > > numbers:

      > >

      > > 1 to 5

      > > 9 to 11

      > > 13

      > > 18 to 20

      > >

      > >

      > > Code is as follows:

      > >

      > > Do While rng.Column <= Sheet2.UsedRange.Columns.Count

      > > i = 1

      > > j = 0

      > > start = rng.Value

      > > nextln = rng.Offset(1, 0).Value

      > > Do While Not nextln = ""

      > > If nextln - start = i Then

      > > i = i + 1

      > > rng.Value = start & " to " & nextln

      > > Cells(rng.Row + 1, rng.Column).Delete (xlShiftUp)

      > > Else *****SEE NOTE BELOW ******

      > > Set rng = rng.Offset(1, 0)

      > > i = 1

      > > start = rng.Value

      > > End If

      > > nextln = rng.Offset(1, 0).Value

      > > Loop

      > > Set rng = Sheet2.Cells(2, rng.Column + 1)

      > > Loop

      > >

      > >

      > > Any help or suggestion is greatly appreciated!

      > >

      > > Thanks,

      > > Mike

      #3; Fri, 23 May 2008 04:38:00 GMT