Convert numbers into intervals

[boboivan]

Hi guys!


Please let me know if I can convert a particular number (e.g. 5) into an interval (1-5) and if I can count them as follows:


Column M Column N
(Number of pages) (Number interval)
2 1-2
5 3-7
3 8-10
... ...


Thanks!

 

[TheCman81]

You're very welcome glad to help

 

[boboivan]

Try this, assuming your data starts in M2 & N2 - You only need to enter the formula in N3 and drag down

Excel 2010

	M	N
Pages	Intervals

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1-2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3-7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8-10[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11-14[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]15-39[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]40-94[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]95-106[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]107-1106[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N2[/TH]
[TD="align: left"]=1&"-"&M2+0[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N3[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N5[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N6[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N7[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N8[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hello there again!

Running some tests today with the formulas, I noticed 2 problems.
1. If the interval has in the left side 1 digit number and in the right side more than 2 digits number, in the next row (the left side of the interval) appears error, as in the example below:

Pages Interval
1 1-1
7 2-8
98 9-106
91 7-197
6 98-203
<table class="wysiwyg_dashes" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" style="width: " width=""><tbody></tbody></table>
2. If I add filters in the table, the intervals remain unchanged, which is not helpful at all to me. Is there anything to do about it?
Thanks a lot!

 

[Legacy 234512]

Fill your M column with page numbers then run this code.

Option Explicit

Sub calc()
    
    Dim nPage As Long
    Dim prevInterval As String
    Dim prevIntervalArr() As String
    Dim newInterval1 As Long
    Dim newInterval2 As Long
    Dim i As Long
    Dim j As Long
    
    Dim ref As Worksheet
    Set ref = ThisWorkbook.Sheets("Sheet1")
    
    For i = 2 To ref.Range("M" & Rows.Count).End(xlUp).Row
            
        If i = 2 Then
            ref.Range("N2").Value = CStr(1 & " - " & ref.Range("M2").Value)
        Else
        
        nPage = ref.Range("M" & i).Value
        
        prevIntervalArr = Split(ref.Range("N" & i).Offset(-1, 0).Value, " - ", 2)
        For j = LBound(prevIntervalArr) To UBound(prevIntervalArr)
            prevInterval = prevIntervalArr(1)
        Next j


        newInterval1 = CLng(prevInterval) + 1
        newInterval2 = newInterval1 + nPage - 1
        
        ref.Range("N" & i).Value = CStr(newInterval1 & " - " & newInterval2)
        End If
    Next i
    
End Sub

ALT + F11 to open VBA editor. Right click to add a new module -> paste this code then hit F5 to run! Good luck

 

[TheCman81]

I've adjusted the formula to handle this. Should work now.

=RIGHT(N2,LEN(N2)-FIND("-",N2))+1&"-"&SUM($M$2:M3)

 

[boboivan]

Fill your M column with page numbers then run this code.

Option Explicit

Sub calc()
    
    Dim nPage As Long
    Dim prevInterval As String
    Dim prevIntervalArr() As String
    Dim newInterval1 As Long
    Dim newInterval2 As Long
    Dim i As Long
    Dim j As Long
    
    Dim ref As Worksheet
    Set ref = ThisWorkbook.Sheets("Sheet1")
    
    For i = 2 To ref.Range("M" & Rows.Count).End(xlUp).Row
            
        If i = 2 Then
            ref.Range("N2").Value = CStr(1 & " - " & ref.Range("M2").Value)
        Else
        
        nPage = ref.Range("M" & i).Value
        
        prevIntervalArr = Split(ref.Range("N" & i).Offset(-1, 0).Value, " - ", 2)
        For j = LBound(prevIntervalArr) To UBound(prevIntervalArr)
            prevInterval = prevIntervalArr(1)
        Next j


        newInterval1 = CLng(prevInterval) + 1
        newInterval2 = newInterval1 + nPage - 1
        
        ref.Range("N" & i).Value = CStr(newInterval1 & " - " & newInterval2)
        End If
    Next i
    
End Sub

ALT + F11 to open VBA editor. Right click to add a new module -> paste this code then hit F5 to run! Good luck

Thanks maestro! Unfortunately it doesn't work. I followed all the steps. When I tried to debug, after pressing F5, the following line of the code has been marked: Set ref = ThisWorkbook.Sheets("Sheet1"). I've changed the name of the sheet and still nothing happens.

 

[Legacy 234512]

Whats your sheet name then? you can go by sheet number instead? Has an error been thrown? what was it

 

[boboivan]

I've adjusted the formula to handle this. Should work now.

=RIGHT(N2,LEN(N2)-FIND("-",N2))+1&"-"&SUM($M$2:M3)

Thank you again real excel guru!
Works perfectly now.

Any idea about the second problem I have? This is very important for the whole idea of the table. When apply filters in my table I'd like the intervals to get changed accordingly to the filtered values in column M.

 

[boboivan]

Whats your sheet name then? you can go by sheet number instead? Has an error been thrown? what was it

The sheet name is 2013. I've tried sheet number, sheet name, all options I know. Nothing! It stills marking the line "Set ref = ThisWorkbook.Sheets("Sheet1")"

 

[Legacy 234512]

Set ref = ThisWorkbook.Sheets("2013")

what error are you getting, run time? what number what does it say

what version of excel are you using

maybe
set ref = ActiveWorkbook.Sheets("2013")

 

[TheCman81]

You mean like this?

=RIGHT(N2,LEN(N2)-FIND("-",N2))+1&"-"&SUBTOTAL(9,$M$2:M3)