List of telephone numbers and need to highlight sequential ranges

[Picillo]

I'm working on a spreadsheet that has a column of telephone numbers. There 1700 numbers and I need to filter/highlight out what numbers are in ranges or produce a list in a different column or worksheet.

So I need something like this.

I'd prefer for a list of the ranges in a different column, but highlighting will do as well.

I have tried the Conditional Formatting method and it doesn't appear to work.

Any help would be greatly appreciated.

Thank you,

 

[Saba Sabaratnam]

My worksheet layout is given below.

Select Cell A2 and do conditional formatting as shown below.

The formula is

=IF(OR(A1=A2-1,A1=A1048576+1),TRUE,"")

Then use format painter to apply the same formatting to other cells.

Kind regards

Saba

 

[Picillo]

My worksheet layout is given below.

View attachment 29844

Select Cell A2 and do conditional formatting as shown below.

The formula is

=IF(OR(A1=A2-1,A1=A1048576+1),TRUE,"")

View attachment 29845
Then use format painter to apply the same formatting to other cells.

Kind regards

Saba

Thank you very much. I figured out that my cells were not formatted as numbers which why nothing was happening. I appreciate your solution.

 

[Picillo]

Does anyone know how to list the ranges in another column or worksheet?

 

[Akuini]

Another option:

Sub a1158545a()
'https://www.mrexcel.com/board/threads/list-of-telephone-numbers-and-need-to-highlight-sequential-ranges.1158545/
Dim i As Long
Dim va, vb

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)

For i = 2 To UBound(va, 1) - 1
 
    If va(i, 1) + 1 = va(i + 1, 1) Then
        vb(i, 1) = va(i, 1)
        vb(i + 1, 1) = va(i + 1, 1)
    End If
    
Next

'result in col B
Range("B1").Resize(UBound(vb, 1), 1) = vb

End Sub

Book1 (version 1).xlsb
	A	B
1	data
2	1	1
3	2	2
4	6
5	8
6	10
7	12	12
8	13	13
9	16	16
10	17	17
11	19
12	24	24
13	25	25
Sheet1

 

[Picillo]

Perfect! Thank you all very much. I love this Forum.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[Picillo]

Another option:

Sub a1158545a()
'https://www.mrexcel.com/board/threads/list-of-telephone-numbers-and-need-to-highlight-sequential-ranges.1158545/
Dim i As Long
Dim va, vb

va = Range("A1", Cells(Rows.Count, "A").End(xlUp))
ReDim vb(1 To UBound(va, 1), 1 To 1)

For i = 2 To UBound(va, 1) - 1

    If va(i, 1) + 1 = va(i + 1, 1) Then
        vb(i, 1) = va(i, 1)
        vb(i + 1, 1) = va(i + 1, 1)
    End If
   
Next

'result in col B
Range("B1").Resize(UBound(vb, 1), 1) = vb

End Sub

Book1 (version 1).xlsb
	A	B
1	data
2	1	1
3	2	2
4	6
5	8
6	10
7	12	12
8	13	13
9	16	16
10	17	17
11	19
12	24	24
13	25	25
Sheet1

Do you know of way produce the same results but to have the beginning of a range in the B column and the end of the range in the C column. e.g.,

Thanks ahead of time for any assistance.

 

[Picillo]

Do you know of way produce the same results but to have the beginning of a range in the B column and the end of the range in the C column. e.g.,

View attachment 29847

Thanks ahead of time for any assistance.

Actually it should look like this.

 

[Akuini]

Try this:

Sub a1158545b()
'https://www.mrexcel.com/board/threads/list-of-telephone-numbers-and-need-to-highlight-sequential-ranges.1158545/
Dim i As Long, j As Long, n As Long
Dim va, vb
Dim flag As Boolean

n = Range("A" & Rows.Count).End(xlUp).Row + 1
va = Range("A1:A" & n)
ReDim vb(1 To UBound(va, 1), 1 To 2)
flag = False

For i = 2 To UBound(va, 1) - 1

    If va(i, 1) + 1 = va(i + 1, 1) Then
        vb(i, 1) = va(i, 1)
        vb(i + 1, 1) = va(i + 1, 1)
        If flag = False Then j = i: flag = True
    Else
        flag = False

    End If
    If i > 2 Then
        If flag = False And va(i - 1, 1) + 1 = va(i, 1) Then vb(j, 2) = va(i, 1)
    End If
Next

'result in col B
Range("B1").Resize(UBound(vb, 1), 2) = vb

End Sub

Book1 (version 1).xlsb
	A	B	C
1	data
2	1	1	2
3	2	2
4	6
5	8
6	10
7	12	12	14
8	13	13
9	14	14
10	17
11	20
12	24	24	25
13	25	25
Sheet1