Search This Blog

Wednesday, November 26, 2008

List to Range in Excel

I know that range means something else in Excel, what we are talking about here is making a list of numbers into a list of ranges. For example, we have :

1 3 4 6 8 9 10 14 15 19 20 22

Imagine we have this on the first column of an Excel sheet. What we want on the second column is:

1 3-4 6 8-10 14-15 19-20 22

Sound dead simple right? Ok, maybe you can skip reading this then. While its sound simple, it took me 15 minutes after a cup of 7-11 Slurpee Brainfreeze to write this :

Option Explicit
Sub Rangemaker()
    Dim i As Long
    Dim col As Long
    Dim sRow As Long
    Dim wRow As Long
    Dim seq As Boolean
    Dim LastRow As Long
    '  speed
    Application.ScreenUpdating = False
    '***begin change***
    'init start row: CHANGE TO SUIT
    sRow = 1
    'set column to put results on: CHANGE TO SUIT
    col = 2
    '***end change***
    'get last row of data to process
    LastRow = Cells(65536, 1).End(xlUp).Row
    'init dest row
    seq = False
    wRow = 1
    'do all rows from sRow
    For i = sRow To LastRow
        'see if next row is continuous
        If CLng(Cells(i + 1, 1)) <> CLng(Cells(i, 1)) + 1 Then
            'Not Continous
            If (seq <> False) Then
                'is part of a seq
                Cells(wRow, col) = Cells(wRow, col) & " - " & Cells(i, 1)
                wRow = wRow + 1
                seq = False
                'is not a seq but single number
                Cells(wRow, col) = Cells(i, 1)
                wRow = wRow + 1
                seq = False
            End If
            If (seq <> True) Then
                Cells(wRow, col) = Cells(i, 1)
            End If
            seq = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Lets run through this a bit. I had added in as much comments as possible. Basically, you can play with 2 variables for your need.
  • sRow which is which row to start making the range.
  • col which is which column the result will appears.
Basically, it checks through each row and the next row and see if they are in continuous sequence. If no, then check if its already part of a sequence, which you should end with a "- ", otherwise then its a single number. If its in continuous sequence, you set some variables and continue.

For those who like insult to injury, to do this in Perl, its simply :
@a= (1,3,4,6,8,9,10,14,15,19,20,22); 
print num2range(@a); 
sub num2range {  
local $_ = join ',' => @_;  
return $_; 

Original idea and post:
Perl code was found from:

No comments:

Amazon Gift Cards!

Thanks for viewing!

Copyright © 2008, All rights reserved.