Beim ersten Auftreten eines Wertes eine 1 in neue Spalte
Hier in diesem Forum wurde eine Frage gestellt, die inhaltlich zusammengefasst folgendes beinhaltet: Eine Excel Datensammlung umfasst knapp 100.000 Datensätze mit unterschiedlichen Werten. In der ersten Spalte sind numerische Gruppen-Bezeichnungen, wo je Gruppe unterschiedlich viele Werte untereinander stehen. Diese numerischen Werte sind zusammengefasst, was die Zielerfüllung etwas erleichtert.
Der Wunsch des Beitrag-Erstellers: In einer neuen Spalte in jeweils der ersten Zeile des Werte-Blocks soll in diese neu erstellte Spalte die Zahl 1 eingetragen werden, sonst soll die Zelle leer bleiben. – Funktionierende Formel-Lösungen wurden angeboten, die Performance ließ aber nach Angabe des Fragestellers sehr zu wünschen übrig. Darum habe ich eine Lösung mit Power Query erstellt.
Sie können natürlich die Datei direkt im Forum herunterladen. Um die große Datenmengen auf eine tragbare Größe zu schrumpfen und auch noch ein wenig „Würze” in den Lösungsweg zu bringen, habe ich einerseits fast alle Spalten mit den Muster-Lösungen entfernt und die Daten als *.csv gespeichert. Mein Muster-File können Sie hier herunterladen.
Beginnen Sie mit einer leeren Excel-Mappe. In Excel 2010⁄2013 werden Sie das Register Power Query aktivieren, in Excel >=2016 / 365 das Register Daten. Je nach Version erstellen Sie nun eine Neue Abfrage, Aus Datei | Aus csv und Importieren die von mir generierte Datei Erste_Zeile_im_Block_1.csv. Power Query erkennt die Codierung und das Trennzeichen korrekt:
Der Dialog für den Import der csv-Datei
Anschließend ein Klick auf Bearbeiten, denn nach dem Import soll ja etwas mit den Daten geschehen. Aus unterschiedlichen Gründen füge ich nun in dieser Abfrage erst einmal eine Index-Spalte hinzu. Dafür im Register ein Klick auf Spalte hinzufügen | Indexspalte. Umgehend wird rechts außen eine neue Spalte mit einem 0‑basierten Index erstellt. Als Überschrift wir automatisch Index vergeben.
Von dieser Abfrage werden Sie nun ein Duplikat erstellen. Hier können Sie nachlesen, wie das machbar ist. Idealerweise werden Sie die neu erstellte Abfrage gleich umbenennen, ich gebe ihr den Namen Erste_Zeile_im_Block_1 (gefiltert), da in dieser Query die Blöcke auf je 1 Zeile gefiltert werden. Und um das zu erreichen, markieren Sie die Spalte Werte und beispielsweise einen Rechtsklick in die Überschrift und im Kontextmenü Duplikate entfernen auswählen. Wenn Sie sich nun die Spalte Index betrachten, dann erkennen Sie rasch, dass diverse Zeilen nicht mehr existieren (sie sind nicht ausgeblendet).
In diese verbliebenen Zeilen soll ja in einer neuen Spalte die Zahl 1 eingetragen werden, in der vollständigen Datei sollen alle anderen Zellen leer bleiben. Aktivieren Sie nun das Register Spalte hinzufügen und Klicken dort im Menüband auf Benutzerdefinierte Spalte. Im nun erscheinenden Dialog vergeben Sie als Neuer Spaltenname beispielsweise Erster Wert oder was auch immer Sie mögen. In Benutzerdefinierte Spaltenformel: geben Sie nach dem Gleichheitszeichen nur die Ziffer 1 ein. Anschließend ein Klick auf OK.
Sie erkennen, dass in jeder der (verbliebenen) Zeilen die Ziffer 1 steht. Jetzt ist es „nur” noch erforderlich, die beiden Abfragen so zusammenzuführen, dass in der Basis-Abfrage in genau diesen Zeilen in einer neuen Spalte die 1 erscheint und die restlichen Zeilen leer bleiben. Dazu aktivieren Sie erst einmal die ursprüngliche, ungefilterte Abfrage. Danach das Register Start und Kombinieren | Abfragen zusammenführen. Es öffnet sich dieser Dialog:
Der Einstig in das Zusammenführen der Abfragen
Nun wählen Sie aus, welches die zu verknüpfende Abfrage sein soll:
Die Auswahl der zweiten Abfrage
Und schlussendlich werden Sie die beiden korrespondierenden Spalten durch einen Klick in die Spalte des jeweiligen „Kastens” markieren:
Beide Index-Spalten sind markiert
Join-Art bleibt so, weil ja alle Zeilen der oberen Abfrage verwendet und aus der unteren die laut Markierung übereinstimmenden verwendet werden sollen. Jetzt noch ein Klick auf OK und der Dialog schließt sich.
Die neue Spalte hat die Überschrift Erste_Zeile_im_Block_1 (gefiltert). Der Inhalt jeder einzelnen Zeile ist Table (Tabelle). Ein Klick auf das Erweitern-Symbol 
in der Überschrift und in einem Dialog werden Ihnen alle Felder aufgezeigt, welchen eingeblendet werden können bzw. sollen. Entfernen Sie das Häkchen bei (Alle Spalten auswählen) und auch bei Ursprünglichen Spaltennamen als Präfix verwenden. Anschließend markieren Sie Erster Wert. Bestätigen Sie mit OK und Sie werden sehen, dass wenige Zeilen dieser Spalte mit einer 1 gefüllt sind und die meisten null enthalten, also leer sind. Sie erkennen aber auch, dass die Reihenfolge nicht mehr stimmt. Darum sortieren Sie die Spalte Index aufsteigend. Jetzt nur noch die eben sortierte Spalte löschen und das Ziel ist erreicht.
Schließen & laden und die beiden Abfragen werden in ein neues Arbeitsblatt gespeichert. Da Sie die gefilterte Version der beiden Abfragen nicht angezeigt haben wollen, können Sie diese Tabelle markieren und löschen. Professioneller wäre es allerdings, wenn Sie diese Abfrage als Nur Verbindung speichern würden und die darzustellende Query als Tabelle. Vielleicht hilft Ihnen da dieser Link. 😎