IP-Adressen sortieren in EXCEL

[Ilkka_2932c2]

Hallo Ihr lieben ExpertInnen,

ich bin es mal wieder mit einer Frage.
Vermutlich sehe ich den Wald vor lauter Bäumen nicht mehr, hihi.

Es dreht sich um Folgendes:

Das Format einer IP-Adresse sieht ja folgendermaßen aus.

xxx.xxx.xxx.xxx (123.456.789.123)
xxx kann ein- oder dreistellig sein.

Auch diese Konstellation kann auftreten:
xxx.xxx.*

Hintergrund:
Ich betreibe einen FTP-Server, der eine SPAM- und Intrusiondetection besitzt. Dieser Wächter sperrt dann die verdächtigen IP´s für die Zukunft.
Mittlerweile hat dieser Wächter schon mehrere Tausend IP´s gesperrt.
(Interessant ist bei den IP-Adressen, das diese meist in den Ostblock- und Fernoststaaten beheimatet sind)
Ähnliche Blöcke fasst der dann im Format „xxx.* oder xxx.xxx.*“ zusammen.
Vergisst aber dabei die einzelnen zu löschen.

Problem:
Nun möchte ich diese IP´s, die ja total unsortiert sind mit EXCEL sortieren.

Komme da allerdings an meine Grenzen. Heisst auf gut Deutsch:
Der sortiert zwar, aber es ist trotzdem unsortiert.

Wer kann mir da einen Tipp geben?

Liebe Grüßle

Ilkka

[Wiz]

Hallo,

dein Problem ist einfach, dass die Sortierung als Text erfolgen musst, wenn Du mit den Adressen inkl. Punkt arbeiten willst, und die geht natürlich dann von links nach rechts, und aufgrund unterschiedlich langer Blöcke gibt dass dann natürlich Chaos. D.h. Du musst die einzelnen Blöcke erst einmal mit führenden Nullen auffüllen, dann klappt es.

Gruß vom Wiz

[abgemeldet5]

Hallo Ilkka

123.456.789.123

Das wird von Excel vermutlich als Zahl mit Zifferngruppierung [Tausendertrennzeichen] interpretiert.
Das passt möglicherweise nicht immer, dann hast du Text und Zahlen in der Spalte durcheinander. Dann wird es nichts mit sortieren.

Versuche mal vorm einfügen in Excel die Spalte komplett als Text zu formatieren.

Gruß Holger

[anon62718186]

Hallo,

zuerst mußt du die fehlenden Blöcke mit Nullen auffüllen.

Dann nutzt du die Funktion Text in Spalten, Trennzeichen „.“
Dann kannst du nach Nummernbereichen sortieren.

Geht auch automatisch mit den Funktionen =LINKS(), TEIL(), =FINDEN() etc.

LG

[Reinhard_fb8cb7]

Hallo Ilkka,

lang ist’s her daß ich davon mal punktuelle Ahnung hatte -)

xxx.xxx.xxx.xxx (123.456.789.123)
xxx kann ein- oder dreistellig sein.

gilt die Beschränkung auf 0 - 255 pro xxx nicht mehr?

Auch diese Konstellation kann auftreten:
xxx.xxx.*

Das entspricht dann dem Adressbereich von
xxx.xxx.0.0 bis xxx.xxx.255.255
Richtig?

Ähnliche Blöcke fasst der dann im Format „xxx.* oder
xxx.xxx.*“ zusammen.
Vergisst aber dabei die einzelnen zu löschen.

Soll nun das Löschen in Excel geschehen?
Oder willste nur sortieren?

Der sortiert zwar, aber es ist trotzdem unsortiert.

Ich habe „ihn“ (den Wächter, nicht. „Er“ erstellet irgedneine Datei,
diese öffnest du mit Excel.
Kannst du diese erstellte Datei mal ins Internet hochladen. Z.B. mit rapidshare o.ä., siehe FAQ:2606

Welche Excelversion hast du?
In welchem Format soll die Datei von Excel wieder abgespeichert werden damit der Wächter sie aus-/einlesen kann?

Geht auch eine Vba-Lösung?

Gruß
Reinhard

[Ilkka_2932c2]

Hallo Ihr Lieben,

Entschuldigung, das ich mich erst jetzt melde, aber die derzeitige Hitze macht mir sehr zu schaffen.

Zum Thema:

Ich habe mir jetzt ein kleines VBA erstellt, was mir zumindest die IP´s richtig mit "0"len auffüllt.
Muss gestehen, das ich mit dem VBA´s auf dem Kriegsfuß stehe, und das Problem mit den "*"chen bislang nicht in den Griff bekommen habe.

Hier mein VBA

Option Explicit
Public Function Korrigiere_IP(zelle)
Dim spl
Dim I As Integer
spl = Split(zelle, „.“)
For I = 0 To UBound(spl)
spl(I) = Format(spl(I), „000“)
Next
Korrigiere_IP = Join(spl, „.“)
End Function

Das funktioniert prächtig, solange die Zelle kein * enthält.
Dummerweise enthalten viele Zellen ein „*“.

Mein Ziel ist es:
Sortieren und doppelte (mehrfache) IP´s zu löschen.

LG
Ilkka

[Reinhard_fb8cb7]

Entschuldigung, das ich mich erst jetzt melde, aber die
derzeitige Hitze macht mir sehr zu schaffen.

Hallo Ilkka,

mir auch ((

Dein Problem ist lösbar. Aber grad wegen der Hitze hab ich keinen Bock auf unnötige Reindenkereien.
Zeige mir eine mappe/tabelle mit Beispieldaten.

Gruß
Reinhard

[Ilkka_2932c2]

Hallo Reinhard,

Dein Problem ist lösbar. Aber grad wegen der Hitze hab ich
keinen Bock auf unnötige Reindenkereien.
Zeige mir eine mappe/tabelle mit Beispieldaten.

vielen Dank für Deine Antwort und Deine Hilfe.
Glaub, das es das einfachste ist, das Du Dir meine Excel-Datei von meinen Server holst.

Hier der Link:
http://milkka.dyndns.org/Oeffentlicher%20Zugang/EXCE…

Viele Grüßle
Ilkka

[Reinhard_fb8cb7]

Dein Problem ist lösbar. Aber grad wegen der Hitze hab ich
keinen Bock auf unnötige Reindenkereien.
Zeige mir eine mappe/tabelle mit Beispieldaten.

Hier der Link:
http://milkka.dyndns.org/Oeffentlicher%20Zugang/EXCE…

Hallo Ilkka,

in B67 klappt deine Funktion nicht, die Zahl erscheint zusammengeschrieben ohne Punkte.
A67 ist als Zahl formatiert, ich nehme an daran liegt es.

Jetzt muß man wissen wie das kam, formatiert der „Wächter“ Zellen mal als Standard, mal als Zahlen oder kam der Fehler durch dich und war nur einmalig?

Gruß
Reinhard

[Ilkka_2932c2]

Guten Morgen Reinhard,

in B67 klappt deine Funktion nicht, die Zahl erscheint
zusammengeschrieben ohne Punkte.
A67 ist als Zahl formatiert, ich nehme an daran liegt es.

Stimmt, Du hast Recht.

Jetzt muß man wissen wie das kam, formatiert der „Wächter“
Zellen mal als Standard, mal als Zahlen oder kam der Fehler
durch dich und war nur einmalig?

Ich habe bei der Erstellung der Datei allerdings keine Formattierung der Zellen vorgenommen.
Also gilt der Wert Standard. Wieso die Zelle A67 eine Formattierung als Zahl bekommen hat ist mir unbekannt. Ich habe diese denfinitiv nicht gemacht. (Warum auch und warum ausgerechnet die Zelle A67?)

Zum „Wächter“: Der generiert im Grunde nur eine Textdatei, wo die verdächtigen IP´s pro Zeile einmal eingetragen wird.
Einmal in der Woche geht der die durch und fasst diese zusammen (Sternchen-Vergabe)
Diese Datei importiere ich einfach in Excel rein.

Grad habe ich mir die Originaldatei (Wächter) genauer angeschaut.
Find dort nichts ungewöhnliches.

Mir schwant nun aber etwas ganz anderes.
Ich hatte mich doch darüber beklagt, das der „Wächter“ nach der zusammenfassung die dann überflüssigen Einträge nicht entfernt.
Vielleicht kann der das Nicht, weil irgendeine Zahl (A67) in der Textdatei ein „irriges“ Format hat?.

Werde das heute Nachmittag überprüfen.
Ich melde mich dann wieder.

Viele Grüßle
Ilkka

[Reinhard_fb8cb7]

http://milkka.dyndns.org/Oeffentlicher%20Zugang/EXCE…

Hallo Ilkka,

ich hab jetzt einfach erstmal A67 gelöscht.
Mach das auch erstmal, kann man sich ja später drum kümmern.

Folgendes Makro „Filtern“ braucht nur die reinen Daten in Spalte A ab Zeile 4.
Das Makro löscht erstmal die Spalten B:E.
Jetzt in der Testphase löscht das Makro noch keine IP-Adressen, markiert aber farbig alle Zeilen die es als löschwürdig erachtet farbig.

Also teste es ausgiebig mit verschiedenen Testdaten in A4:Ax
auch mit sowas:
112.240.123.45
112.240.123.*

also dem Sternchen an verschiedenen Positionen der IP-Adresse.
Kann sein, daß die verbunden zellen in A1:B1 stören beim Sortieren, heb dann die Verbundenheit auf, ich mag das sowieso nicht.

Option Explicit
'
Sub Filtern()
Dim Zei As Long, N As Integer
Zei = Cells(Rows.Count, 1).End(xlUp).Row
Range("B:E").Clear
Range("B4").FormulaLocal = "=A4"
Range("B4").AutoFill Destination:=Range("B4:B" & Zei)
Range("B4:B" & Zei).Value = Range("B4:B" & Zei).Value
Range("B4:B" & Zei).TextToColumns Destination:=Range("B4"), DataType:=xlDelimited, \_
 TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, \_
 Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar \_
 :=".", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Range("A4:E" & Zei).Sort Key1:=Range("C4"), Order1:=xlAscending, Key2:=Range("D4") \_
 , Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= \_
 xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A4:E" & Zei).Sort Key1:=Range("B4"), Order1:=xlAscending, \_
 Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
For N = 5 To 3 Step -1
 Call Faerbe(Zei, N)
Next N
End Sub
'
Sub Faerbe(ByVal Zei As Long, ByVal Spa As Long)
Dim Z As Long, Z2 As Long
For Z = Zei To 5 Step -1
 Z2 = Z - 1
 While Cells(Z, 2).Value = Cells(Z2, 2).Value
 If Cells(Z, Spa).Value = "\*" And Cells(Z2, Spa).Value "" Then
 If Cells(Z2, Spa).Value "\*" Then
 Range(Cells(Z2, 1), Cells(Z2, 5)).Interior.ColorIndex = 34
 End If
 End If
 Z2 = Z2 - 1
 Wend
Next Z
End Sub

Gruß
Reinhard