Find missing values in a range of 1-254

[GregDz11]

I have 2 columns with Ip address information:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.1[/TD]
[TD]101[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]95[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1.1.2[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I'd like to return for every grouping of column 1 the missing values from 1-254 in column 2

So for example, an output of the above data would show something like:
1.1.1.1 to 1.1.1.99
1.1.1.102 to 1.1.1.254
1.1.2.1 to 1.1.2.94
1.1.2.96 to 1.1.2.199
1.1.2.201 to 1.1.2.254

 

[dreid1011]

Welcome to the board.

Perhaps this:

Sub FillIn()
Dim i As Long, j As Long
Dim lRow As Long
lRow = Range("C" & Rows.Count).End(xlUp).Row + 1
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    For j = 1 To 254
        If Range("A" & i).Value <> Range("A" & i - 1).Value Then
            If j < Range("B" & i).Value Then
                Range("C" & lRow).Value = Range("A" & i).Value & "." & j
                lRow = lRow + 1
            End If
        Else
            If j > Range("B" & i - 1).Value And j <> Range("B" & i).Value Then
                Range("C" & lRow).Value = Range("A" & i).Value & "." & j
                lRow = lRow + 1
            End If
        End If
    Next j
Next i
End Sub

This assumes that the data begins in column A, row 2 and that all the values in the first column are sorted. Also, this code should be installed in the sheet module where your data is.

 

[GregDz11]

Pleasure to be here, and thank you for the response.

It seems to repeat the output at the next number in the series, but then skips that missing number

I've posted the results in google sheets:
https://docs.google.com/spreadsheets/d/1iUoBwv7VyqEcQDllQ0Iu9iEf_w1U1YKwMio8d4vndPY/edit?usp=sharing

Let me know if you're able to access. Thank you again!

 

[GregDz11]

This is what I've been working on, not as far as you got

Sub MultiColumnTable_To_Array()

Dim myTable As ListObject
Dim myArray As Variant
Dim myInactive As Variant
Dim strArray() As String
Dim strInactive As String
Dim x As Long
Dim y As Long
Dim z As Long




  Set myTable = ActiveSheet.ListObjects("Table13")
  y = 0
  myArray = myTable.DataBodyRange


  For x = LBound(myArray) To UBound(myArray)
    'Debug.Print myArray(x, 3)
    y = x + 1
    If x = UBound(myArray) Then
        strInactive = strInactive & "; " & myArray(x, 3)
    ElseIf myArray(x, 3) <> myArray(y, 3) Then
         strInactive = strInactive & "; " & myArray(x, 3)
    End If
  Next x
strInactive = Right(strInactive, Len(strInactive) - 2)
   Debug.Print strInactive


strArray = Split(strInactive, "; ")


For x = LBound(strArray) To UBound(strArray)
    Debug.Print strArray(x)
Next x


    
End Sub

 

[dreid1011]

It seemed to have been working on the sample you provided. I am not sure why it's being skewed with the larger set of data.

 

[dreid1011]

Nvm, I know why it's skewing. But I don't have the time to play with it at the moment.

 

[GregDz11]

okay, thanks for the attempt, appreciate it.

 

[GregDz11]

Was able to do it with this solution:

=SMALL(IF(COUNTIF($A$1:$A$254,ROW($1:$254))=0,ROW($1:$254),""),ROW(A1))

Explanation here: https://www.myonlinetraininghub.com/excel-list-missing-numbers-in-a-sequence

Only thing now would be to format into ranges similar to this:
1.1.1.1 to 1.1.1.99
1.1.1.102 to 1.1.1.254
1.1.2.1 to 1.1.2.94
1.1.2.96 to 1.1.2.199
1.1.2.201 to 1.1.2.254

 

[dreid1011]

Glad you got it sorted.