Finding free numbers in a list of servernames

[youhbi]

Hi guys

I just can't come up with a solution, I was hopeing maybe one of you had an idea.

We got the following data. Where Servername is the given list and all the other are just the same column but splittet into it's logical parts. I just thought I'd make it easier to understand and create a working formula. So they're really just helper rows.

Servername	Type	Country	Number
ZZ ABC 100 10001	ZZABC	100	10001
ZZ ABC 100 10003	ZZABC	100	10003
ZZ ABC 200 10001	ZZABC	200	10001
ZZ ABC 200 10005	ZZABC	200	10005
ZZ DEF 100 10001	ZZDEF	100	10001
ZZ DEF 300 10001	ZZDEF	300	10001

I'm looking for a result similiar to this

A1		ZZ ABC 100	ZZ ABC 200	ZZ DEF 100	ZZ DEF 300
A2	Highest free	ZZ ABC 100 10004	ZZ ABC 200 10006	ZZ DEF 100 10002	ZZ DEF 300 10002
A3	Free inbetween 1	ZZ ABC 100 10002	ZZ ABC 200 10002
A4	Free inbetween 2		ZZ ABC 200 10003
A5	Free inbetween 3		ZZ ABC 200 10004

* I only added spaces for readability

ZZ ABC 100 10001
ZZ = Always the same
ABC = Type of Server (Application, DC, DB etc.)
100 = Internal country code
10001 = Ongoing number (this is what I'm interested in)

As you can see in row "2", it would be great to not only get all missing numbers for each "type" (A3:A5), but also the single one after the last one actually in use.

I want to be able to take a look at the result and easily see which one are still free. If I have a single server I might just use a number inbetween (rows 3-5).
Example:
ZZ ABC 100 10003

If I got a clustered system, I might look for the smallest common number in two countries which are both free. So I would look in row "2", in both countries (100 & 200).
Example:
ZZ ABC 100 10006 [&] ZZ ABC 200 10006

Tricky part number is to get the values in row "1" dynamically generated aswell. So get all Unique <Type+Country> combinations.
If I had to create them manually, it wouldn't be as convenient as I intended, but I could live with that.

I guess in general we gotta find all the missing numbers, which isn't to hard, but in combination with respecting also it's type. If we'd just look the last 5 digits, we'd get wrong results, becuase "ZZABC10010001 != ZZABC20010001 OR ZZDEF10010001".

I hope this all makes any sense at all to someone not familiar with our naming convention.

Thank you very much for everyone taking a look into this!

Cheers!

 

[shknbk2]

Welcome to the forum.

I did something recently where I could re-use some of my code to make it easier. When I make a worksheet having the data exactly as your first table, I can create a new sheet with a table similar to your second table.

You mention that the spaces are there for readability. However, I don't know what the data would look like without the spaces. I was only able to base my code on the spaces being there. So, this is probably a good start, but not the finished product. We'll have to tweak it if the server names aren't so nicely spaced. If your actual data is different from your first table, at least make a new sheet with the data based on your first table with the spaces to get the feel of how the code works.

The code may not be as minimal as possible either, but it works with my test data.

Sub AvailableServers()
    Dim c1 As Collection
    Dim cServers As Collection
    Dim rServers As Range
    Dim a As Variant
    Dim i As Integer, j As Integer, k As Integer
    Dim ServerNumber As Integer
    Dim HighestFree As Integer
    Dim InBetween() As Integer
    Dim iInB As Integer
    Dim sh As Worksheet
    Dim leftPart As String
    
    Set rServers = Range("A1").Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count).Offset(1, 0)
    a = rServers.Value
    Set c1 = New Collection
    Set cServers = New Collection
    For i = LBound(a, 1) To UBound(a, 1)
        c1.Add a(i, 1)
    Next
    Do While c1.Count > 0
        leftPart = Left(c1(1), InStrRev(c1(1), " ") - 1)
        cServers.Add leftPart
        For i = c1.Count To 2 Step -1
            If Left(c1.Item(i), Len(leftPart)) = leftPart Then
                c1.Remove i
            End If
        Next i
        c1.Remove 1
    Loop
    
    Set sh = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
    Range("A2").Value = "Highest free"
    For j = 1 To cServers.Count
        Erase InBetween
        For i = c1.Count To 1 Step -1
            c1.Remove i
        Next i
        For i = LBound(a, 1) To UBound(a, 1)
            If Left(a(i, 1), Len(cServers(j))) = cServers(j) Then
                c1.Add Mid(a(i, 1), Len(cServers(j)) + 2)
            End If
        Next i
        iInB = -1
        HighestFree = 10001
        k = 1
        Do While k <= c1.Count
            If c1(k) = HighestFree Then
                k = k + 1
            Else
                iInB = iInB + 1
                ReDim Preserve InBetween(iInB)
                InBetween(iInB) = HighestFree
            End If
            HighestFree = HighestFree + 1
        Loop
        HighestFree = c1(c1.Count) + 1
        Range("A1").Offset(0, j).Value = cServers(j)
        Range("A2").Offset(0, j).Value = cServers(j) & " " & HighestFree
        If iInB > -1 Then
            For k = 1 To UBound(InBetween) + 1
                Range("A2").Offset(k, 0).Value = "Free inbetween " & k
                Range("A2").Offset(k, j).Value = cServers(j) & " " & InBetween(k - 1)
            Next k
        End If
    Next j
End Sub