9
Eine Such-, bzw Filterfunktion in Excel
Eine Zeichenfolge in Excel nach Konsistenz prüfen
Hallo liebe Admnistrator-Gemeinde,
folgender Sachverhalt:
in einer Excel-Spalte werden Zeichenfolgen folgenden Formats eingegeben:
2/312001
2/312002
2/312003
wobei die Zahl 2 vor dem Schrägstrich und der Schrägstrich selbst immer und unverändert bleiben.
Die Zahlenkombination nach dem Schrägstrich ist sechsstellig und sollte vortlaufend (konsistent) sein.
Nun kommt es in der Realität, dass eine oder mehrere sechstelligen Zahlen übersprungen, nicht eingetragen werden.
In der Spalte kommen am Ende einigen Tausende solcher Zeichenkommbinationen.
Gibt es eine Möglichkeit in Excel (VBA, Filters, Funktionen etc.) die fehlenden, nicht geschriebenen Zahlen anzuzeigen?
So liebe Gemeinde, bitte helft mir
P.S. einen guten Rutsch wünsche ich euch allen
folgender Sachverhalt:
in einer Excel-Spalte werden Zeichenfolgen folgenden Formats eingegeben:
2/312001
2/312002
2/312003
wobei die Zahl 2 vor dem Schrägstrich und der Schrägstrich selbst immer und unverändert bleiben.
Die Zahlenkombination nach dem Schrägstrich ist sechsstellig und sollte vortlaufend (konsistent) sein.
Nun kommt es in der Realität, dass eine oder mehrere sechstelligen Zahlen übersprungen, nicht eingetragen werden.
In der Spalte kommen am Ende einigen Tausende solcher Zeichenkommbinationen.
Gibt es eine Möglichkeit in Excel (VBA, Filters, Funktionen etc.) die fehlenden, nicht geschriebenen Zahlen anzuzeigen?
So liebe Gemeinde, bitte helft mir
P.S. einen guten Rutsch wünsche ich euch allen
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 178216
Url: https://administrator.de/contentid/178216
Printed on: April 25, 2024 at 19:04 o'clock
9 Comments
Latest comment
Hallo Cybercult!
Da Du nicht beschrieben hast, in welcher Form Du das Ergebnis benötigst, nur einmal ein schematischer Ansatz in VBA:
Es wird hier anhand der Angaben in den Zeilen 2 - 4 zunächst aus der ersten Datenzeile die Zahl nach "2/" ausgelesen und dann für alle weiteren Zeilen (bis eine leere Zelle in Spalte A gefunden wird) geprüft, ob die Zahl der nächsten Laufnummer (also etwa "312004") entspricht - falls nicht, wird eine Leerzeile eingefügt. Die Prüfung erfolgt konkret so, dass die ersten beiden Stellen übersprungen werden und nur der Rest des Zellinhaltes verglichen wird; es würde daher etwa "3/312004" ebenso wie zB "//312004" akzeptiert werden. Falls Du eine exakte Kontrolle des gesamten Zellinhaltes benötigst, müsstest Du zunächst klar stellen, was für den Fall, dass die konstanten ersten (beiden) Stellen nicht stimmen, passieren soll ...
Ach ja - vorausgesetzt wird auch, dass die Spalte A bereits sortiert wurde ...
Grüße
bastla
Da Du nicht beschrieben hast, in welcher Form Du das Ergebnis benötigst, nur einmal ein schematischer Ansatz in VBA:
Sub Check()
AbZeile = 2 'Werte ab dieser Zeile
Spalte = "A" 'Werte in dieser Spalte
Anfang = "2/" 'Konstanter Anfangsteil der Werte
Inhalt = Cells(AbZeile, Spalte).Value 'Inhalt der ersten Zelle auslesen
WertLfd = Mid(Inhalt, Len(Anfang) + 1) 'Zahlenwert der ersten Zeile ermitteln
Zeile = AbZeile + 1 'nach erster Zeile mit Prüfung beginnen
Inhalt = Cells(Zeile, Spalte).Value 'Zelleninhalt auslesen
Do While Inhalt <> "" 'wiederholen bis zur ersten leeren Zelle
WertLfd = WertLfd + 1 'Zahlenwert erhöhen
'Wenn Zellinhalt nicht nach dem Anfangsteil den richtigen Zahlenwert enthält, Leerzeile einfügen
If Mid(Inhalt, Len(Anfang) + 1) <> WertLfd Then Rows(Zeile).Insert
Zeile = Zeile + 1 'nächste Zeile
Inhalt = Cells(Zeile, Spalte).Value 'Zelleninhalt auslesen
Loop
End SubAch ja - vorausgesetzt wird auch, dass die Spalte A bereits sortiert wurde ...
Grüße
bastla
Moin,
ich würde sowas quick 'n dirty lösen, Du mußt ja nur wissen, wo was fehlt ...
einfach über right() / left() den konstanten Teil eliminieren und die sechsstellige Zahl raussortieren (in einer neuen Spalte) und dann checken, ob in der darunter stehenden Zelle der Nachfolger steht - wenn ja, iss okay, ansonsten irgendwas in die Zeile reinschreiben ....
Gruß
24
ich würde sowas quick 'n dirty lösen, Du mußt ja nur wissen, wo was fehlt ...
einfach über right() / left() den konstanten Teil eliminieren und die sechsstellige Zahl raussortieren (in einer neuen Spalte) und dann checken, ob in der darunter stehenden Zelle der Nachfolger steht - wenn ja, iss okay, ansonsten irgendwas in die Zeile reinschreiben ....
Gruß
24
Moin Cybercult,
ich würde den Killefitz jetzt zwar eher quick & dirty mit zwei Matrixformeln lösen als gleich mit einem ausgewachsenen Makro...
Nichtsdestotrotz deckt natürlich auch bastlas Lösung die Anforderung ab.
Jedenfalls soweit ich bei wabernden Nebelschwaden überhaupt das Wort "Abdecken" missbrauchen würde.
Eine punktgenaue Lösung erfordert eben auch ein paar feste Punkte im Raum - siehe oben in bastlas Einleitung.
Gibt doch mal Zwischenfeedback, was du mit den Kommentaren anfangen konntest, was dir vielleicht noch Erwähnenswertes eingefallen ist inzwischen und was fehlt.
Grüße
Biber
ich würde den Killefitz jetzt zwar eher quick & dirty mit zwei Matrixformeln lösen als gleich mit einem ausgewachsenen Makro...
Nichtsdestotrotz deckt natürlich auch bastlas Lösung die Anforderung ab.
Jedenfalls soweit ich bei wabernden Nebelschwaden überhaupt das Wort "Abdecken" missbrauchen würde.
Eine punktgenaue Lösung erfordert eben auch ein paar feste Punkte im Raum - siehe oben in bastlas Einleitung.
Gibt doch mal Zwischenfeedback, was du mit den Kommentaren anfangen konntest, was dir vielleicht noch Erwähnenswertes eingefallen ist inzwischen und was fehlt.
Grüße
Biber
mit etwas Verspätung möchte mich für die Mühe recht herzlich bedanken. Der Ansatz ist völlig OK. Alle Zahlen können mit "2/" oder seltener mit "--/" beginnen.
In Ordnung ist es auch eine Leerzeile einzufügen wenn eine (oder mehrere) vorlaufenden Nummern fehlen.
Hier ein Beispiel:
+--------------+
|2/312328 |
+--------------+
|2/312329 |
+--------------+
|2/312330 |=> hier kann eine Leerzeile eingefügt werden da 2/312331 fehlt
+--------------+
|2/312332 |
+--------------+
|2/312334 |=> hier können Leerzeile für alle fehlenden zahlen zwishen 312334 und 312346 eingefügt werden, usw...
+--------------+
|2/312346 |
+--------------+
|2/312349 |
+--------------+
Die Spalte mit den zu untersuchenden Nummern ist aufsteigend sortiert.
Den Quellcode habe ich in den VBA-Editor übernommen (bin kein VBA-Experte eher habe ich mit C und C++ gearbeitet) und wenn ich den Sub laufen lasse endet
das ganze in einer endless loop und nur mit dem task manager komme ich raus.
Mache ich da was falsch..?
Nochmals danke für die Mühe.
In Ordnung ist es auch eine Leerzeile einzufügen wenn eine (oder mehrere) vorlaufenden Nummern fehlen.
Hier ein Beispiel:
+--------------+
|2/312328 |
+--------------+
|2/312329 |
+--------------+
|2/312330 |=> hier kann eine Leerzeile eingefügt werden da 2/312331 fehlt
+--------------+
|2/312332 |
+--------------+
|2/312334 |=> hier können Leerzeile für alle fehlenden zahlen zwishen 312334 und 312346 eingefügt werden, usw...
+--------------+
|2/312346 |
+--------------+
|2/312349 |
+--------------+
Die Spalte mit den zu untersuchenden Nummern ist aufsteigend sortiert.
Den Quellcode habe ich in den VBA-Editor übernommen (bin kein VBA-Experte eher habe ich mit C und C++ gearbeitet) und wenn ich den Sub laufen lasse endet
das ganze in einer endless loop und nur mit dem task manager komme ich raus.
Mache ich da was falsch..?
Nochmals danke für die Mühe.
Hallo Biber,
danke für die Antwort. leider kann ich mir nichts unter quick&dirty lösung vorstellen. Hast du vielleicht ein Paar Ansetze parat?
Die lösung von bastla hat leider auch nicht fuktioniert, möglicherweise habe ich da was falsch gemacht.
Siehe oben meine Antwort an den bastla vielleicht kanns Du das ganze in einer Excel-Tabelle nachstellen..
Danke nochmals
danke für die Antwort. leider kann ich mir nichts unter quick&dirty lösung vorstellen. Hast du vielleicht ein Paar Ansetze parat?
Die lösung von bastla hat leider auch nicht fuktioniert, möglicherweise habe ich da was falsch gemacht.
Siehe oben meine Antwort an den bastla vielleicht kanns Du das ganze in einer Excel-Tabelle nachstellen..
Danke nochmals
Moin Cybercult,
danke für deine nachgereichten Informationen
Interessant, wie total unterschiedlich deine Anforderung von bastla einerseits und mir andererseits aufgefasst wurde.
Ich war, auch aufgrund deines Satzes "Gibt es eine Möglichkeit in Excel .... die fehlenden, nicht geschriebenen Zahlen anzuzeigen?" davon ausgegangen, dass mindestens das Tabellenblatt mit vergebenen Nummern, wenn nicht sogar die ganze Original-Exceldatei "read-only" ist.
Schon allein weil Leerzeilen ( bzw. Zeilen bestehend nur aus diesen Auftrags-, Inventar-, Bestell-, Kamasutra- oder WTF-Nummern) ohne jegliche weitere Daten sicherlich niemals nicht problemlos von Folgeverarbeitungen hingenommen werden würden.
Mein Ansatz wäre also gewesen, in einem neuen Tabellenblatt (oder einer neuen Exceldatei) einen Abgleich des Soll-Nummernkreises mit dem vergebenen Ist-Nummernkreis zu machen.
Und nur alle nicht (oder noch nicht) vergebenen Nummern untereinander in einer Spalten aufzulisten.
Für dieses Zielwäre ich mit zweieinhalb Matrixformeln wohl auch hingekommen.
Wenn du aber -wie von bastla skizziert- in der Original-Liste die fehlenden Zeilen an den richtigen Positionen eingefügt haben möchtest
--> dann ist ein Makro sinnvoller.
Ich hatte eine andere Vorgehensweise angenommen und deshalb natürlich auch eine andere Lösung.
Grüße
Biber
danke für deine nachgereichten Informationen
Interessant, wie total unterschiedlich deine Anforderung von bastla einerseits und mir andererseits aufgefasst wurde.
Ich war, auch aufgrund deines Satzes "Gibt es eine Möglichkeit in Excel .... die fehlenden, nicht geschriebenen Zahlen anzuzeigen?" davon ausgegangen, dass mindestens das Tabellenblatt mit vergebenen Nummern, wenn nicht sogar die ganze Original-Exceldatei "read-only" ist.
Schon allein weil Leerzeilen ( bzw. Zeilen bestehend nur aus diesen Auftrags-, Inventar-, Bestell-, Kamasutra- oder WTF-Nummern) ohne jegliche weitere Daten sicherlich niemals nicht problemlos von Folgeverarbeitungen hingenommen werden würden.
Mein Ansatz wäre also gewesen, in einem neuen Tabellenblatt (oder einer neuen Exceldatei) einen Abgleich des Soll-Nummernkreises mit dem vergebenen Ist-Nummernkreis zu machen.
Und nur alle nicht (oder noch nicht) vergebenen Nummern untereinander in einer Spalten aufzulisten.
Für dieses Zielwäre ich mit zweieinhalb Matrixformeln wohl auch hingekommen.
Wenn du aber -wie von bastla skizziert- in der Original-Liste die fehlenden Zeilen an den richtigen Positionen eingefügt haben möchtest
--> dann ist ein Makro sinnvoller.
Ich hatte eine andere Vorgehensweise angenommen und deshalb natürlich auch eine andere Lösung.
Grüße
Biber
Hallo Cybercult!
Mit der folgenden Zeile 14 sollte das dann doch noch funktionieren:
Grüße
bastla
Mit der folgenden Zeile 14 sollte das dann doch noch funktionieren:
If CLng(Mid(Inhalt, Len(Anfang) + 1)) <> WertLfd Then Rows(Zeile).Insert
nur mit dem task manager komme ich raus.
Die Tastenkombination Strg+Pause hilft in solchen Fällen ...Grüße
bastla
Hallo bastla,
ich bin Dir zum besten, herzlichsten Dank verfpfichtet. Diesmal hat alles wunderbar funktioniert. Testweise habe ich einen Block von etwa 6500 Zeilen prüfen lassen.
In wenigen Sekunden wurden die Zeilen eingefügt wo die Nummern gefehlt hatten. Besten Dank.
P.S. habe sehr gerne mit C und C++ gearbeitet, gegen VBA habe ich mich doch wenig gewehrt , jetzt aber wo ich den Vorteile sehe, wenn man(n) es so gut kann wie Du bekomme ich doch Lust es zu lernen. Wie oder wo kann man das am besten lernen?
ich bin Dir zum besten, herzlichsten Dank verfpfichtet. Diesmal hat alles wunderbar funktioniert. Testweise habe ich einen Block von etwa 6500 Zeilen prüfen lassen.
In wenigen Sekunden wurden die Zeilen eingefügt wo die Nummern gefehlt hatten. Besten Dank.
P.S. habe sehr gerne mit C und C++ gearbeitet, gegen VBA habe ich mich doch wenig gewehrt , jetzt aber wo ich den Vorteile sehe, wenn man(n) es so gut kann wie Du bekomme ich doch Lust es zu lernen. Wie oder wo kann man das am besten lernen?
Hallo Cybercult!
Grüße
bastla
Wie oder wo kann man das am besten lernen?
Soferne Dich speziell VBA für Excel interessiert, könntest Du Dir zB VBA in Excel - Grundlagen anschauen, wobei in vielen Fällen noch nicht einmal VBA nötig sein sollte - siehe zB excelformeln.de ...Grüße
bastla
