Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are all
listed in the A column. We only need to report the unknown ranges for this
numbers, not the actual numbers. How can I do that?

Doing this manually after sorting the data is taking us forever.

Here's an example, I cut and paste a super-small portion of the sorted data
here:

41222
41223
41224
41225
41226
41227
41762
41763
41764
41765
41766
41767
41768
41769
41770
41771
41772
41773
41774
41775
41776
41777
42302
42303
42304
42305
42306
42307
42308
42309
42310
42311
42312
42313
42314
42315
42316
42830
42831
42832
42833
42834
42835
42836
42837
42838
42839
42842
42843
42844
42845
42846
42847
42848

And this is what we need:

Range 1: 41222-41227
Range 2: 41762-41777
Range 3: 42302-42316
Range 4: 42830-42848
etc...

Any suggestions?

You said you wanted the UNknown ranges.

In B2:

=IF(A2-A1=1,"",A1&" - "&A2)

and fill down.

This formula has to be modified somewhat to report the known ranges, as in your example.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Quco" <(E-Mail Removed)> wrote in message news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
| Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are all
| listed in the A column. We only need to report the unknown ranges for this
| numbers, not the actual numbers. How can I do that?
|
| Doing this manually after sorting the data is taking us forever.
|
| Here's an example, I cut and paste a super-small portion of the sorted data
| here:
|
| 41222
| 41223
| 41224
| 41225
| 41226
| 41227
| 41762
| 41763
| 41764
| 41765
| 41766
| 41767
| 41768
| 41769
| 41770
| 41771
| 41772
| 41773
| 41774
| 41775
| 41776
| 41777
| 42302
| 42303
| 42304
| 42305
| 42306
| 42307
| 42308
| 42309
| 42310
| 42311
| 42312
| 42313
| 42314
| 42315
| 42316
| 42830
| 42831
| 42832
| 42833
| 42834
| 42835
| 42836
| 42837
| 42838
| 42839
| 42842
| 42843
| 42844
| 42845
| 42846
| 42847
| 42848
|
| And this is what we need:
|
| Range 1: 41222-41227
| Range 2: 41762-41777
| Range 3: 42302-42316
| Range 4: 42830-42848
| etc...
|
| Any suggestions?
|

The code below may do what you want. It will scan the numbers in column A of
Sheet1 and put the start and end number of each series of sequential numbers
out to Sheet2, with the starting number of the block in Sheet2 column A and
ending number of each block in Sheet2 column B. Change the lines of code
marked with '<<< to your specific needs.

Sub LookAtLists()

Dim StartRow As Long ' data begins in this row
Dim EndRow As Long ' data ends in this row (calculated)
Dim Temp As Long
Dim RowNdx As Long
Dim Dest As Range ' results are written starting in this cell
Dim DataColumn As String ' data is in this column
Dim WS As Worksheet ' data resides on this worksheet
Dim SaveStart As Long

StartRow = 1 '<<< CHANGE
DataColumn = "A" '<<< CHANGE
Set WS = Worksheets("Sheet1") '<<< CHANGE
Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE

With WS ' note the leading periods in the lines of code below
EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
SaveStart = .Cells(StartRow, DataColumn)
For RowNdx = StartRow + 1 To EndRow
If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
Then
Dest(1, 1) = SaveStart
Dest(1, 2) = .Cells(RowNdx, DataColumn).Value
SaveStart = .Cells(RowNdx + 1, DataColumn).Value
Set Dest = Dest(2, 1)
End If
Next RowNdx
End With

End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)Sub LookAtLists()



"Quco" <(E-Mail Removed)> wrote in message
news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
> Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are
> all
> listed in the A column. We only need to report the unknown ranges for this
> numbers, not the actual numbers. How can I do that?
>
> Doing this manually after sorting the data is taking us forever.
>
> Here's an example, I cut and paste a super-small portion of the sorted
> data
> here:
>
> 41222
> 41223
> 41224
> 41225
> 41226
> 41227
> 41762
> 41763
> 41764
> 41765
> 41766
> 41767
> 41768
> 41769
> 41770
> 41771
> 41772
> 41773
> 41774
> 41775
> 41776
> 41777
> 42302
> 42303
> 42304
> 42305
> 42306
> 42307
> 42308
> 42309
> 42310
> 42311
> 42312
> 42313
> 42314
> 42315
> 42316
> 42830
> 42831
> 42832
> 42833
> 42834
> 42835
> 42836
> 42837
> 42838
> 42839
> 42842
> 42843
> 42844
> 42845
> 42846
> 42847
> 42848
>
> And this is what we need:
>
> Range 1: 41222-41227
> Range 2: 41762-41777
> Range 3: 42302-42316
> Range 4: 42830-42848
> etc...
>
> Any suggestions?
>

Chip,

I'm learning to read code, I inserted the code as a Module in Sheet 1 (where
I have the data listed starting in cell A1). Then I went to view > macros >
run macro and selected the title LookAtLists() to run it.

It runs, but stops saying there is a syntaxis error at this cell:

If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)

In the meantime I'm going to close and open the excel spreadsheet without
saving changes. Maybe I need to activate the macros? but I thought I did that.



"Chip Pearson" wrote:

> The code below may do what you want. It will scan the numbers in column A of
> Sheet1 and put the start and end number of each series of sequential numbers
> out to Sheet2, with the starting number of the block in Sheet2 column A and
> ending number of each block in Sheet2 column B. Change the lines of code
> marked with '<<< to your specific needs.
>
> Sub LookAtLists()
>
> Dim StartRow As Long ' data begins in this row
> Dim EndRow As Long ' data ends in this row (calculated)
> Dim Temp As Long
> Dim RowNdx As Long
> Dim Dest As Range ' results are written starting in this cell
> Dim DataColumn As String ' data is in this column
> Dim WS As Worksheet ' data resides on this worksheet
> Dim SaveStart As Long
>
> StartRow = 1 '<<< CHANGE
> DataColumn = "A" '<<< CHANGE
> Set WS = Worksheets("Sheet1") '<<< CHANGE
> Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE
>
> With WS ' note the leading periods in the lines of code below
> EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
> SaveStart = .Cells(StartRow, DataColumn)
> For RowNdx = StartRow + 1 To EndRow
> If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
> Then
> Dest(1, 1) = SaveStart
> Dest(1, 2) = .Cells(RowNdx, DataColumn).Value
> SaveStart = .Cells(RowNdx + 1, DataColumn).Value
> Set Dest = Dest(2, 1)
> End If
> Next RowNdx
> End With
>
> End Sub
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)Sub LookAtLists()
>
>
>
> "Quco" <(E-Mail Removed)> wrote in message
> news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
> > Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are
> > all
> > listed in the A column. We only need to report the unknown ranges for this
> > numbers, not the actual numbers. How can I do that?
> >
> > Doing this manually after sorting the data is taking us forever.
> >
> > Here's an example, I cut and paste a super-small portion of the sorted
> > data
> > here:
> >
> > 41222
> > 41223
> > 41224
> > 41225
> > 41226
> > 41227
> > 41762
> > 41763
> > 41764
> > 41765
> > 41766
> > 41767
> > 41768
> > 41769
> > 41770
> > 41771
> > 41772
> > 41773
> > 41774
> > 41775
> > 41776
> > 41777
> > 42302
> > 42303
> > 42304
> > 42305
> > 42306
> > 42307
> > 42308
> > 42309
> > 42310
> > 42311
> > 42312
> > 42313
> > 42314
> > 42315
> > 42316
> > 42830
> > 42831
> > 42832
> > 42833
> > 42834
> > 42835
> > 42836
> > 42837
> > 42838
> > 42839
> > 42842
> > 42843
> > 42844
> > 42845
> > 42846
> > 42847
> > 42848
> >
> > And this is what we need:
> >
> > Range 1: 41222-41227
> > Range 2: 41762-41777
> > Range 3: 42302-42316
> > Range 4: 42830-42848
> > etc...
> >
> > Any suggestions?
> >
>

Still haven't figured out what to do at this point. I did save the Excel
spreadsheet as *.xlsm to keep the macro available. For now I'll continue to
work on the spreadsheet manually.



"Quco" wrote:

> Chip,
>
> I'm learning to read code, I inserted the code as a Module in Sheet 1 (where
> I have the data listed starting in cell A1). Then I went to view > macros >
> run macro and selected the title LookAtLists() to run it.
>
> It runs, but stops saying there is a syntaxis error at this cell:
>
> If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
>
> In the meantime I'm going to close and open the excel spreadsheet without
> saving changes. Maybe I need to activate the macros? but I thought I did that.
>
>
>
> "Chip Pearson" wrote:
>
> > The code below may do what you want. It will scan the numbers in column A of
> > Sheet1 and put the start and end number of each series of sequential numbers
> > out to Sheet2, with the starting number of the block in Sheet2 column A and
> > ending number of each block in Sheet2 column B. Change the lines of code
> > marked with '<<< to your specific needs.
> >
> > Sub LookAtLists()
> >
> > Dim StartRow As Long ' data begins in this row
> > Dim EndRow As Long ' data ends in this row (calculated)
> > Dim Temp As Long
> > Dim RowNdx As Long
> > Dim Dest As Range ' results are written starting in this cell
> > Dim DataColumn As String ' data is in this column
> > Dim WS As Worksheet ' data resides on this worksheet
> > Dim SaveStart As Long
> >
> > StartRow = 1 '<<< CHANGE
> > DataColumn = "A" '<<< CHANGE
> > Set WS = Worksheets("Sheet1") '<<< CHANGE
> > Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE
> >
> > With WS ' note the leading periods in the lines of code below
> > EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
> > SaveStart = .Cells(StartRow, DataColumn)
> > For RowNdx = StartRow + 1 To EndRow
> > If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
> > Then
> > Dest(1, 1) = SaveStart
> > Dest(1, 2) = .Cells(RowNdx, DataColumn).Value
> > SaveStart = .Cells(RowNdx + 1, DataColumn).Value
> > Set Dest = Dest(2, 1)
> > End If
> > Next RowNdx
> > End With
> >
> > End Sub
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting
> > www.cpearson.com
> > (email on the web site)Sub LookAtLists()
> >
> >
> >
> > "Quco" <(E-Mail Removed)> wrote in message
> > news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
> > > Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They are
> > > all
> > > listed in the A column. We only need to report the unknown ranges for this
> > > numbers, not the actual numbers. How can I do that?
> > >
> > > Doing this manually after sorting the data is taking us forever.
> > >
> > > Here's an example, I cut and paste a super-small portion of the sorted
> > > data
> > > here:
> > >
> > > 41222
> > > 41223
> > > 41224
> > > 41225
> > > 41226
> > > 41227
> > > 41762
> > > 41763
> > > 41764
> > > 41765
> > > 41766
> > > 41767
> > > 41768
> > > 41769
> > > 41770
> > > 41771
> > > 41772
> > > 41773
> > > 41774
> > > 41775
> > > 41776
> > > 41777
> > > 42302
> > > 42303
> > > 42304
> > > 42305
> > > 42306
> > > 42307
> > > 42308
> > > 42309
> > > 42310
> > > 42311
> > > 42312
> > > 42313
> > > 42314
> > > 42315
> > > 42316
> > > 42830
> > > 42831
> > > 42832
> > > 42833
> > > 42834
> > > 42835
> > > 42836
> > > 42837
> > > 42838
> > > 42839
> > > 42842
> > > 42843
> > > 42844
> > > 42845
> > > 42846
> > > 42847
> > > 42848
> > >
> > > And this is what we need:
> > >
> > > Range 1: 41222-41227
> > > Range 2: 41762-41777
> > > Range 3: 42302-42316
> > > Range 4: 42830-42848
> > > etc...
> > >
> > > Any suggestions?
> > >
> >

The code got line wrapped in the news post. The line you are having
problems with should end with the word "Then" and the "Then" on the next
line should be deleted.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Quco" <(E-Mail Removed)> wrote in message
news:B88BDB14-B57F-4A20-97A3-(E-Mail Removed)...
> Chip,
>
> I'm learning to read code, I inserted the code as a Module in Sheet 1
> (where
> I have the data listed starting in cell A1). Then I went to view > macros
> >
> run macro and selected the title LookAtLists() to run it.
>
> It runs, but stops saying there is a syntaxis error at this cell:
>
> If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
>
> In the meantime I'm going to close and open the excel spreadsheet without
> saving changes. Maybe I need to activate the macros? but I thought I did
> that.
>
>
>
> "Chip Pearson" wrote:
>
>> The code below may do what you want. It will scan the numbers in column A
>> of
>> Sheet1 and put the start and end number of each series of sequential
>> numbers
>> out to Sheet2, with the starting number of the block in Sheet2 column A
>> and
>> ending number of each block in Sheet2 column B. Change the lines of code
>> marked with '<<< to your specific needs.
>>
>> Sub LookAtLists()
>>
>> Dim StartRow As Long ' data begins in this row
>> Dim EndRow As Long ' data ends in this row (calculated)
>> Dim Temp As Long
>> Dim RowNdx As Long
>> Dim Dest As Range ' results are written starting in this cell
>> Dim DataColumn As String ' data is in this column
>> Dim WS As Worksheet ' data resides on this worksheet
>> Dim SaveStart As Long
>>
>> StartRow = 1 '<<< CHANGE
>> DataColumn = "A" '<<< CHANGE
>> Set WS = Worksheets("Sheet1") '<<< CHANGE
>> Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE
>>
>> With WS ' note the leading periods in the lines of code below
>> EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
>> SaveStart = .Cells(StartRow, DataColumn)
>> For RowNdx = StartRow + 1 To EndRow
>> If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1,
>> DataColumn)
>> Then
>> Dest(1, 1) = SaveStart
>> Dest(1, 2) = .Cells(RowNdx, DataColumn).Value
>> SaveStart = .Cells(RowNdx + 1, DataColumn).Value
>> Set Dest = Dest(2, 1)
>> End If
>> Next RowNdx
>> End With
>>
>> End Sub
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting
>> www.cpearson.com
>> (email on the web site)Sub LookAtLists()
>>
>>
>>
>> "Quco" <(E-Mail Removed)> wrote in message
>> news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
>> > Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They
>> > are
>> > all
>> > listed in the A column. We only need to report the unknown ranges for
>> > this
>> > numbers, not the actual numbers. How can I do that?
>> >
>> > Doing this manually after sorting the data is taking us forever.
>> >
>> > Here's an example, I cut and paste a super-small portion of the sorted
>> > data
>> > here:
>> >
>> > 41222
>> > 41223
>> > 41224
>> > 41225
>> > 41226
>> > 41227
>> > 41762
>> > 41763
>> > 41764
>> > 41765
>> > 41766
>> > 41767
>> > 41768
>> > 41769
>> > 41770
>> > 41771
>> > 41772
>> > 41773
>> > 41774
>> > 41775
>> > 41776
>> > 41777
>> > 42302
>> > 42303
>> > 42304
>> > 42305
>> > 42306
>> > 42307
>> > 42308
>> > 42309
>> > 42310
>> > 42311
>> > 42312
>> > 42313
>> > 42314
>> > 42315
>> > 42316
>> > 42830
>> > 42831
>> > 42832
>> > 42833
>> > 42834
>> > 42835
>> > 42836
>> > 42837
>> > 42838
>> > 42839
>> > 42842
>> > 42843
>> > 42844
>> > 42845
>> > 42846
>> > 42847
>> > 42848
>> >
>> > And this is what we need:
>> >
>> > Range 1: 41222-41227
>> > Range 2: 41762-41777
>> > Range 3: 42302-42316
>> > Range 4: 42830-42848
>> > etc...
>> >
>> > Any suggestions?
>> >
>>

Thank you! I wanted to ask for your advice about what book can I get to learn
more about Macros and Excel. I really need to start writing some useful code
for our office!

"Chip Pearson" wrote:

> The code got line wrapped in the news post. The line you are having
> problems with should end with the word "Then" and the "Then" on the next
> line should be deleted.
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
>
> "Quco" <(E-Mail Removed)> wrote in message
> news:B88BDB14-B57F-4A20-97A3-(E-Mail Removed)...
> > Chip,
> >
> > I'm learning to read code, I inserted the code as a Module in Sheet 1
> > (where
> > I have the data listed starting in cell A1). Then I went to view > macros
> > >
> > run macro and selected the title LookAtLists() to run it.
> >
> > It runs, but stops saying there is a syntaxis error at this cell:
> >
> > If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1, DataColumn)
> >
> > In the meantime I'm going to close and open the excel spreadsheet without
> > saving changes. Maybe I need to activate the macros? but I thought I did
> > that.
> >
> >
> >
> > "Chip Pearson" wrote:
> >
> >> The code below may do what you want. It will scan the numbers in column A
> >> of
> >> Sheet1 and put the start and end number of each series of sequential
> >> numbers
> >> out to Sheet2, with the starting number of the block in Sheet2 column A
> >> and
> >> ending number of each block in Sheet2 column B. Change the lines of code
> >> marked with '<<< to your specific needs.
> >>
> >> Sub LookAtLists()
> >>
> >> Dim StartRow As Long ' data begins in this row
> >> Dim EndRow As Long ' data ends in this row (calculated)
> >> Dim Temp As Long
> >> Dim RowNdx As Long
> >> Dim Dest As Range ' results are written starting in this cell
> >> Dim DataColumn As String ' data is in this column
> >> Dim WS As Worksheet ' data resides on this worksheet
> >> Dim SaveStart As Long
> >>
> >> StartRow = 1 '<<< CHANGE
> >> DataColumn = "A" '<<< CHANGE
> >> Set WS = Worksheets("Sheet1") '<<< CHANGE
> >> Set Dest = Worksheets("Sheet2").Range("A1") '<<< CHANGE
> >>
> >> With WS ' note the leading periods in the lines of code below
> >> EndRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
> >> SaveStart = .Cells(StartRow, DataColumn)
> >> For RowNdx = StartRow + 1 To EndRow
> >> If .Cells(RowNdx, DataColumn) + 1 <> .Cells(RowNdx + 1,
> >> DataColumn)
> >> Then
> >> Dest(1, 1) = SaveStart
> >> Dest(1, 2) = .Cells(RowNdx, DataColumn).Value
> >> SaveStart = .Cells(RowNdx + 1, DataColumn).Value
> >> Set Dest = Dest(2, 1)
> >> End If
> >> Next RowNdx
> >> End With
> >>
> >> End Sub
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting
> >> www.cpearson.com
> >> (email on the web site)Sub LookAtLists()
> >>
> >>
> >>
> >> "Quco" <(E-Mail Removed)> wrote in message
> >> news:9938BC85-513A-41B1-995C-(E-Mail Removed)...
> >> > Hi, I have a LOOOONNNG list of 5-digit numbers in a spreadsheet. They
> >> > are
> >> > all
> >> > listed in the A column. We only need to report the unknown ranges for
> >> > this
> >> > numbers, not the actual numbers. How can I do that?
> >> >
> >> > Doing this manually after sorting the data is taking us forever.
> >> >
> >> > Here's an example, I cut and paste a super-small portion of the sorted
> >> > data
> >> > here:
> >> >
> >> > 41222
> >> > 41223
> >> > 41224
> >> > 41225
> >> > 41226
> >> > 41227
> >> > 41762
> >> > 41763
> >> > 41764
> >> > 41765
> >> > 41766
> >> > 41767
> >> > 41768
> >> > 41769
> >> > 41770
> >> > 41771
> >> > 41772
> >> > 41773
> >> > 41774
> >> > 41775
> >> > 41776
> >> > 41777
> >> > 42302
> >> > 42303
> >> > 42304
> >> > 42305
> >> > 42306
> >> > 42307
> >> > 42308
> >> > 42309
> >> > 42310
> >> > 42311
> >> > 42312
> >> > 42313
> >> > 42314
> >> > 42315
> >> > 42316
> >> > 42830
> >> > 42831
> >> > 42832
> >> > 42833
> >> > 42834
> >> > 42835
> >> > 42836
> >> > 42837
> >> > 42838
> >> > 42839
> >> > 42842
> >> > 42843
> >> > 42844
> >> > 42845
> >> > 42846
> >> > 42847
> >> > 42848
> >> >
> >> > And this is what we need:
> >> >
> >> > Range 1: 41222-41227
> >> > Range 2: 41762-41777
> >> > Range 3: 42302-42316
> >> > Range 4: 42830-42848
> >> > etc...
> >> >
> >> > Any suggestions?
> >> >
> >>
>