Sub M_snb()
' put all values in column A of sheet2 into an array; put that array into variable 'sn'
sn = Sheet2.Columns(1).SpecialCells(2)
' create a new object (Dictionary) that only can contain unique keys.
' we will add new keys based on the information in the variable sn, that contains an array with all values in column1 in sheet2.
' a key identical to an existing one will be ignored.
' the dictionary object stores a list of unique keys in the property .keys, that is a 1-dimensional array
' since you can't write a 1-dimensional array to a column in a worksheet, you will have to convert it to a 2-dimensiional one first; using the Excel function 'Transpose'.
With CreateObject("scripting.dictionary") ' create a new dictionary
For j = 1 To UBound(sn) ' create indexnumbers to get access to every item in the array
x0 = .Item("host " & Split(Mid(sn(j, 1), InStr(sn(j, 1), "/") + 1), "(")(0) & " host " & Replace(Split(Mid(sn(j, 1), InStr(InStr(sn(j, 1), "/") + 1, sn(j, 1), "/") + 1), ")")(0), "(", " eq "))
' the string in every item of aray sn: sn(1,1), sn(2,1), sn(3,1), sn(4,1)... etc. will be
' - truncated after the first occurrence of the forward slash "/": Mid(sn(j, 1), InStr(sn(j, 1))
' - the remaining string at the right side is being split by the character "(" : split(....,"(")
' - the first portion of the splitting (0) is being taken: it is the first IP-address
' - before this IP_address we simply put the string "host "; and after it we do the same: & " host "
' - now we want to truncate the string at the second occurrence of the forward slash in the string.
' - we do that by indicating the forward slash has to be sought after where the first forwardslash has been found: InStr(InStr(sn(j, 1), "/") + 1, sn(j, 1), "/") + 1)
' - we truncate the string after the second occurrence of the forwardslash: Mid(sn(j,1), ...)
' - the remaining string at the right side is being split by the character ")": split( ... ,")")
' - the first portion of the splitting (0) is being taken: it is the second IP-address, containing an "(" to indicate the port and the portnumber
' - the character "(" will be replaced by the string " eq ": replace( ..., "("," eq ")
' - this result will be added to the first IP-address to constitute a key that can be added to the dictionary
' - we add a key to a dictionary using the method x0 (that is arbirary) = .item( "keystring" )
' - only unique keys will be accepted by the dictionary: so the list of keys always consists of unique keys
Next
Sheet2.Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
' - take cell C1 in sheet2 and enlarge its range in column C so it can contain all keys (.count) in the Dictionary
' - fill that range after converting the 1-dimensional array .keys into a 2-dimensional array with 'transpose'.
End With
End Sub
pfffff