Find missing values in a range of 1-254

GregDz11

New Member
Joined
Mar 13, 2017
Messages
14
I have 2 columns with Ip address information:
Column1Column 2Column 3
1.1.1100
1.1.1101
1.1.295
1.1.2200

<tbody>
</tbody>

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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Welcome to the board.

Perhaps this:

Code:
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.
 
Upvote 0
This is what I've been working on, not as far as you got

Code:
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
 
Upvote 0
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.
 
Upvote 0
Nvm, I know why it's skewing. But I don't have the time to play with it at the moment.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top