Lücken in IDs, Kundennummern, … füllen
Hinweis:
Die Arbeitsschritte dieses Beitrages sind in einem kleinen Video dokumentiert. Das unterstützende Begleit-Video finden Sie auf YouTube an dieser Stelle.
Prinzipiell sollten durch Löschen entstanden Lücken in IDs, Auftragsnummern, etc. nicht wieder aufgefüllt werden. Mitunter ist es aber der Wunsch oder auch unumgänglich, dass aus einer „gebrochenen” Liste von eigentlich fortlaufenden Zahlen eine Aufstellung gestellt wird, wo beispielsweise jede ID oder andere numerische Wert lückenlos aufgeführt ist, die dazugehörigen Werte jedoch leer sind.
Bei einigen wenigen Datensätzen wie in dieser Mustertabelle ist das prinzipiell kein Problem, einige Zeilen einzufügen. Bei sehr umfangreichen Listen kann das so aufwendig sein, dass eine Lösung auf dem genannten Wege nicht rentabel ist. In jedem Fall lässt sich so etwas per VBA/Makro lösen. Beginnend mit der Version Excel 2010 steht Ihnen das Add-In Power Query zur Verfügung. Damit ist ein Erfolg rasch und ohne eine Zeile Code gegeben.
Wenn Sie die Musterdatei geladen haben werden Sie erkennen, dass diverse Nummern fehlen. In Anlehnung an das Prinzip „2 Tabellen vergleichen” können Sie in wenigen Schritten eine neue Tabelle mit fortlaufenden, lückenlosen IDs (oder entsprechend Kundennummern, Rechnungsnummern, …) erstellen, wo die eingefügten Zeilen ausschließlich die bislang fehlende Nummer enthalten. Gehen Sie dazu so vor:
- Falls noch nicht geschehen, formatieren sie die Daten als Intelligente Tabelle/Liste
- Sortieren Sie erforderlichenfalls die Spalte ID nach aufsteigenden Werten
- Merken Sie sich den niedrigsten und den höchsten Wert der ID
- Lassen Sie eine Spalte frei und tragen Sie in die 1. Zeile (hier: E1) den Text ID2 als Überschrift ein.
- In E2 tragen sie den niedrigsten Wert, hier im Beispiel also 1000 ein.
- Markieren Sie erforderlichenfalls noch einmal E2 und im Menü Start, Gruppe Bearbeiten | Füllbereich | Reihe…, Reihe in → Spalten und tragen bei Endwert den Wert der höchsten ID ein. Alternative: Ziehen Sie einfach die Zelle mit gedrückter Taste Strg so weit nach unten, bis der Endwert erreicht ist.
- Formatieren Sie auch diesen Bereich als Tabelle.
Damit ist die Vorbereitung abgeschlossen. Markieren Sie eine Zelle in der Tabelle mit den fehlenden Werten und öffnen Sie diese entweder über den Menüpunkt Power Query oder das Menü Daten, Auswahl Aus Tabelle, um die Daten im Abfrage-Editor verarbeiten zu können. Änderungen sind hier nicht unbedingt erforderlich, darum können Sie die Abfrage direkt wieder Schließen und laden. Natürlich hätten sie vorher in der 3. Spalte Geburtstag den Datentyp auf Datum ändern können, damit nicht in jeder Zeile die Uhrzeit angezeigt wird; Sie können das aber auch im nächsten Schritt noch später machen.
Wechseln Sie nun wieder zum ersten Tabellenblatt, markieren einen beliebigen Wert in der Tabelle mit der ID2 und öffnen Sie diese auch im Abfrage Editor. Wahrscheinlich sehen sie jetzt im Editor nur diese Abfrage. Falls Sie sich einen Überblick über die Gesamtheit der Abfragen dieser Mappe verschaffen wollen, Klicken Sie auf das > ganz links im Fenster oberhalb des Textes Abfragen und Ihnen werden alle aktuellen Abfragen angezeigt:
Alle Abfragen anzeigen
Die aktuelle Abfrage ist hier durch grüne Hinterlegung markiert. – Wählen Sie nun im Menü das Symbol Kombinieren | Abfragen zusammenführen. Im neuen Dialogfenster wählen Sie im leeren Dropdown Tabelle1 als zweite Abfrage aus:
Auswahl der zweiten Abfrage für das Zusammenführen
Übrigens: Auch wenn dort immer „Tabelle#” steht, es sind die Namen der jeweiligen Abfrage und nicht die ursprüngliche Tabelle. Nun markieren sie durch Klick jeweils die Spalten ID und ID2, damit beide markiert sind. Ein Klick auf OK und das ganze sieht dann so aus:
Angefügte Abfrage, erster Schritt
Klicken Sie in der Überschrift NewColumn auf den Doppelpfeil rechts und entfernen Sie im Dialogfenster erst einmal das Häkchen bei ID. Zusätzlich können Sie auch noch das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden löschen; das hat aber nur Einfluss auf die neue Überschrift. Das Ergebnis ist nun genau das, was sie erwartet haben. Falls Sie es nicht schon von getan haben, sollten Sie nun in der Spalte Geburtstag den Datentyp auf Datum ändern. Vorzugsweise sollten Sie die Überschrift ID2 auf ID ändern, weil dieses ja dem Original entspricht. Schließen und laden und im neuen Tabellenblatt erkennen Sie eine Liste mit fortlaufenden, lückenlosen IDs und entsprechend leeren Feldern an den Positionen, die hinzugefügt worden sind. Das Ziel ist erreicht.