Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.

this should do the job:

---------------------------------------------
Sub consol()

Dim f_number As Long
Dim l_number As Long
Dim sh_ As Worksheet

Set sh_ = Worksheets("sheet2")

f_number = Cells(2, 1)
l_number = Cells(2, 2)

j = 1

For i = 3 To Cells(65536, 1).End(xlUp).Row
If Cells(i, 1).Value < l_number + 1 Then
sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number
f_number = Cells(i, 1)
j = j + 1
End If
l_number = Cells(i, 2)
Next i

sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number

End Sub
-------------------------------------------------------

exchange sheet2 with whatever your output sheet should be.

the sheet you want to consolidate has to be the activesheet, otherwise
it won't work (i was to lazy to do it properly, sorry!)

hth

Carlo

On Dec 6, 11:22 am, FJ wrote:
Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.

Hi, Carlo, thank you so much. :) Your macro worked great! :)



"carlo" wrote:

this should do the job:

---------------------------------------------
Sub consol()

Dim f_number As Long
Dim l_number As Long
Dim sh_ As Worksheet

Set sh_ = Worksheets("sheet2")

f_number = Cells(2, 1)
l_number = Cells(2, 2)

j = 1

For i = 3 To Cells(65536, 1).End(xlUp).Row
If Cells(i, 1).Value < l_number + 1 Then
sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number
f_number = Cells(i, 1)
j = j + 1
End If
l_number = Cells(i, 2)
Next i

sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number

End Sub
-------------------------------------------------------

exchange sheet2 with whatever your output sheet should be.

the sheet you want to consolidate has to be the activesheet, otherwise
it won't work (i was to lazy to do it properly, sorry!)

hth

Carlo

On Dec 6, 11:22 am, FJ wrote:
Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.

You're very welcome

Carlo

On Dec 6, 7:53 pm, Chartreuse
wrote:
Hi, Carlo, thank you so much. :) Your macro worked great! :)



"carlo" wrote:
this should do the job:

---------------------------------------------
Sub consol()

Dim f_number As Long
Dim l_number As Long
Dim sh_ As Worksheet

Set sh_ = Worksheets("sheet2")

f_number = Cells(2, 1)
l_number = Cells(2, 2)

j = 1

For i = 3 To Cells(65536, 1).End(xlUp).Row
If Cells(i, 1).Value < l_number + 1 Then
sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number
f_number = Cells(i, 1)
j = j + 1
End If
l_number = Cells(i, 2)
Next i

sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number

End Sub
-------------------------------------------------------

exchange sheet2 with whatever your output sheet should be.

the sheet you want to consolidate has to be the activesheet, otherwise
it won't work (i was to lazy to do it properly, sorry!)

hth

Carlo

On Dec 6, 11:22 am, FJ wrote:
Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.- Hide quoted text -

- Show quoted text -

Hi, Carlo, thanks again for your help the other day. I actually have another
question about this macro. We've been given a new project. It is the same
as the old one you wrote the macro for. The only difference is that this
time the data has letters in front of the numbers. Is there any way to
change the macro so that it would do the same thing as before but include the
letters in front of the numbers?

In other words, we have the following data:

Beginning # Ending #
XYZ60220718 XYZ60220719
XYZ60220720 XYZ60220720
XYZ60220721 XYZ60220731
XYZ60220732 XYZ60220732
XYZ60220733 XYZ60220757
FMTY40006024 FMTY40006025
FMTY40006026 FMTY40006029
FMTY40006030 FMTY40006031

And ultimately they would like it to look as follows:

Beginning # Ending #
XYZ60220718 XYZ60220757
FMTY40006024 FMTY40006031

I tried to do it by first separating the text from the numbers, then running
the macro, then recombining the text and the numbers using formulas, but for
reasons that are difficult to explain here I don't seem to get the correct
result with all the entries when everything is recombined. I guess maybe
this sort of thing would have to be written into the macro code, although I
don't know. Unfortunately, I have almost no knowledge of VBA.

Thank you in advance for any help.




"carlo" wrote:

You're very welcome

Carlo

On Dec 6, 7:53 pm, Chartreuse
wrote:
Hi, Carlo, thank you so much. :) Your macro worked great! :)



"carlo" wrote:
this should do the job:

---------------------------------------------
Sub consol()

Dim f_number As Long
Dim l_number As Long
Dim sh_ As Worksheet

Set sh_ = Worksheets("sheet2")

f_number = Cells(2, 1)
l_number = Cells(2, 2)

j = 1

For i = 3 To Cells(65536, 1).End(xlUp).Row
If Cells(i, 1).Value < l_number + 1 Then
sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number
f_number = Cells(i, 1)
j = j + 1
End If
l_number = Cells(i, 2)
Next i

sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number

End Sub
-------------------------------------------------------

exchange sheet2 with whatever your output sheet should be.

the sheet you want to consolidate has to be the activesheet, otherwise
it won't work (i was to lazy to do it properly, sorry!)

hth

Carlo

On Dec 6, 11:22 am, FJ wrote:
Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.- Hide quoted text -

- Show quoted text -

Hi FJ

that should be possible.
But i have some questions concerning the structu
is it possible, that there are numbers between the letters??
-- like AAA9A99999
Are your fields sorted according to the letters?

I will tell you as soon as i have some code.

cheers carlo

On Dec 7, 7:16 pm, FJ wrote:
Hi, Carlo, thanks again for your help the other day. I actually have another
question about this macro. We've been given a new project. It is the same
as the old one you wrote the macro for. The only difference is that this
time the data has letters in front of the numbers. Is there any way to
change the macro so that it would do the same thing as before but include the
letters in front of the numbers?

In other words, we have the following data:

Beginning # Ending #
XYZ60220718 XYZ60220719
XYZ60220720 XYZ60220720
XYZ60220721 XYZ60220731
XYZ60220732 XYZ60220732
XYZ60220733 XYZ60220757
FMTY40006024 FMTY40006025
FMTY40006026 FMTY40006029
FMTY40006030 FMTY40006031

And ultimately they would like it to look as follows:

Beginning # Ending #
XYZ60220718 XYZ60220757
FMTY40006024 FMTY40006031

I tried to do it by first separating the text from the numbers, then running
the macro, then recombining the text and the numbers using formulas, but for
reasons that are difficult to explain here I don't seem to get the correct
result with all the entries when everything is recombined. I guess maybe
this sort of thing would have to be written into the macro code, although I
don't know. Unfortunately, I have almost no knowledge of VBA.

Thank you in advance for any help.



"carlo" wrote:
You're very welcome

Carlo

On Dec 6, 7:53 pm, Chartreuse
wrote:
Hi, Carlo, thank you so much. :) Your macro worked great! :)

"carlo" wrote:
this should do the job:

---------------------------------------------
Sub consol()

Dim f_number As Long
Dim l_number As Long
Dim sh_ As Worksheet

Set sh_ = Worksheets("sheet2")

f_number = Cells(2, 1)
l_number = Cells(2, 2)

j = 1

For i = 3 To Cells(65536, 1).End(xlUp).Row
If Cells(i, 1).Value < l_number + 1 Then
sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number
f_number = Cells(i, 1)
j = j + 1
End If
l_number = Cells(i, 2)
Next i

sh_.Cells(j, 1) = f_number
sh_.Cells(j, 2) = l_number

End Sub
-------------------------------------------------------

exchange sheet2 with whatever your output sheet should be.

the sheet you want to consolidate has to be the activesheet, otherwise
it won't work (i was to lazy to do it properly, sorry!)

hth

Carlo

On Dec 6, 11:22 am, FJ wrote:
Hi, I have I have a spreadsheet with about 65,000 rows. I have to
consolidate the data so that ranges of numbers with no breaks (in other
words, where the numbers increase by one) will appear in one cell. The data
looks something like this:

Beginning # Ending #
60220718 60220719
60220720 60220720
60220721 60220731
60220732 60220732
60220733 60220757
40006024 40006025
40006026 40006029
40006030 40006031

Basically, what I have to do is consolidate the data so that it will look
like this:

60220718-60220757
40006024-40006031

Is there any way to write a formula or a macro to do this?

Thanks in advance for any information.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -