VBA: alle Werte in Spalte finden

Forumthread: VBA: alle Werte in Spalte finden

08.09.2021 12:09:40

Adrian

https://www.herber.de/bbs/user/147962.xlsx
Hallo,
ich benötige Hilfe bei einem Problem.
In Spalte A stehen Sachnummern, denen 2 Kennungen zugeordnet werden. Identische Sachnummern können öfter vorkommen.
Mein Wunsch ist es, dass in Zelle E2 nach der Sachnummer in Zelle A2 in der Spalte A gesucht wird und alle Kennungen für die Sachnummer zusammenfasst.
Eine umgesetzte Lösung mit For Each ist leider zu langsam, da die Liste mehrer tausend Zeilen umfasst.
Über jede Hilfe wäre ich sehr dankbar.
Adrian

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA: alle Werte in Spalte finden

08.09.2021 12:33:00

Oberschlumpf
Alternative Power-Query

08.09.2021 12:42:23

ChrisL
AW: VBA: alle Werte in Spalte finden

08.09.2021 12:57:38

Daniel

AW: VBA: alle Werte in Spalte finden

08.09.2021 12:33:00

Oberschlumpf

Hi Adrian
wieso soll in E4 nur 532 10 stehen? A4 hat doch den gleichen Eintrag wie A2 + A6.
Ciao
Thorsten

Alternative Power-Query

08.09.2021 12:42:23

ChrisL

Hi Adrian
Für Grosse Datenmengen empfiehlt sich m.E. Power-Query:
https://www.herber.de/bbs/user/147965.xlsx
Die Differenz im Wunschergebnis ignoriere ich.
Bei Bedarf bitte Power-Query Intro schauen und die Einzelschritte in der Beispieldatei nachverfolgen. Bei Fragen/Unklarheiten stehe ich bzw. das Forum zur Verfügung.
Ansonsten bitte die Frage wieder als offen markieren. Für eine VBA-Lösung empfehle ich dir die Liste vorgängig zu sortieren. Zwar musst du weiterhin die Zeilen einzeln durchlaufen, aber der Umfang pro Nummer ist beschränkt. Als weitere Optimierungsmöglichkeit könntest du mittels Array/Datenfeldern arbeiten und das Ergebnis erst zum Abschluss in die Tabelle übertragen. Auch wenn du damit den Prozess beschleunigen kannst, wird die Abarbeitung etwas Zeit beanspruchen.
cu
Chris

AW: VBA: alle Werte in Spalte finden

08.09.2021 12:57:38

Daniel

HI
probier mal diese Formeln in E2.


=B2&" "&C2&WENNFEHLER(";"&SVERWEIS(A2;A3:E$9999;5;0);"")

die vollständige Zusammenfassung steht dann immer beim ersten Vorkommen.
wenn der zusammengefasste wert dann noch bei allen A123 stehen soll, in F2 diese Formel:


=SVERWEIS(A2;A:E;5;0)

beide Formeln müssen natürlich bis nach unten gezogen werden.
sollte das nicht schnell genug sein, sortiere die Liste nach Spalte A
dann kommt in E2 die Formel:


="B2&" "&C2&Wenn(A2=A3;"; "&E3;"")

in F2 die Formel:


=wenn(A2=A1;F1;E2)

falls du wieder umsortieren willst, müsstest du die Formeln kopieren und als Wert einfügen.
solltest du nach der Zusammenfassung nur noch für jede Nummer eine Zeile mit dem Zusammengefassten Kennungen haben wollen, wende einfach das Duplikate Entfernen auf die ganze Tabelle an mit der Nummer als Kennung.
Da die Zusammenfassung oben steht, bleibt dann diese Zeile übrig.
Gruß Daniel

Infobox / Tutorial

Werte in Spalte mit VBA und Formeln zusammenfassen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um alle Werte in einer Spalte zu finden und zusammenzufassen, kannst du entweder VBA oder Formeln verwenden. Hier ist eine Schritt-für-Schritt-Anleitung für beide Methoden:

VBA-Methode:

Öffne den Visual Basic for Applications (VBA) Editor mit ALT + F11.
Füge ein neues Modul hinzu und kopiere folgenden Code hinein:

Sub Zusammenfassen()
   Dim ws As Worksheet
   Set ws = ThisWorkbook.Sheets("Tabelle1") ' Tabelle anpassen

   Dim lastRow As Long
   lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

   Dim i As Long
   Dim result As String
   Dim currentValue As String

   For i = 2 To lastRow
       currentValue = ws.Cells(i, 1).Value
       If result = "" Then
           result = ws.Cells(i, 2).Value & " " & ws.Cells(i, 3).Value
       Else
           result = result & "; " & ws.Cells(i, 2).Value & " " & ws.Cells(i, 3).Value
       End If
       ws.Cells(i, 5).Value = result ' Ergebnis in Spalte E
   Next i
End Sub

Passe die Tabelle an und führe das Makro aus.

Formel-Methode:
- In Zelle E2 kannst du folgende Formel eingeben:
```
=B2 & " " & C2 & WENNFEHLER(";" & SVERWEIS(A2;A3:E$9999;5;0);"")
```
- Ziehe die Formel nach unten, um sie auf alle relevanten Zellen anzuwenden.

Häufige Fehler und Lösungen

Fehler: Wert wird nicht gefunden.
- Lösung: Überprüfe, ob die Sachnummern in Spalte A korrekt eingegeben sind. Achte auf Leerzeichen oder andere unsichtbare Zeichen.
Fehler: Zu langsame Ausführung.
- Lösung: Sortiere die Liste vor der Ausführung des VBA-Skripts. Das verbessert die Performance erheblich.
Fehler: Falsches Ergebnis in Spalte E.
- Lösung: Stelle sicher, dass die Formeln korrekt sind und bis zur letzten Zeile gezogen wurden.

Alternative Methoden

Eine alternative Methode zur Zusammenfassung von Werten ist die Verwendung von Power Query. Diese Technik ist besonders effektiv bei großen Datenmengen und ermöglicht eine einfachere Datenmanipulation. Du kannst die Schritte in der Power-Query-Dokumentation nachlesen oder die Beispiel-Datei aus dem Forum verwenden.

Praktische Beispiele

Hier ist ein Beispiel, wie du die oben genannten Methoden anwendest:

VBA Beispiel: Wenn du eine Liste von Sachnummern hast, die in Spalte A beginnen und die zugehörigen Kennungen in Spalten B und C stehen, wird das VBA-Skript die Kennungen in Spalte E für jede Sachnummer zusammenfassen.
Formel Beispiel: Wenn deine Daten wie folgt aussehen:

A	B	C
A123	532	10
A123	533	20
A124	534	30

Dann zeigt die Formel in E2 das Ergebnis: 532 10; 533 20.

Tipps für Profis

Nutzung von Arrays: Wenn du mit sehr großen Datenmengen arbeitest, kann die Verwendung von Arrays in VBA die Geschwindigkeit der Verarbeitung erheblich erhöhen.
Datenvalidierung: Stelle sicher, dass die Daten in Spalte A keine Duplikate enthalten, wenn das Ergebnis eindeutig sein soll.
Ergebnisse in eine neue Tabelle schreiben: Du kannst die Ergebnisse auch in eine neue Tabelle schreiben, um die Übersichtlichkeit zu erhöhen.

FAQ: Häufige Fragen

1. Wie kann ich das VBA-Skript anpassen? Du kannst die Variablen im Skript ändern, um andere Spalten oder Tabellen zu referenzieren.

2. Was tun, wenn die Formeln nicht die erwarteten Ergebnisse liefern? Überprüfe die Zellreferenzen und die Eingabewerte auf Korrektheit. Achte darauf, dass alle Formeln bis zur letzten Zeile gezogen werden.

3. Funktioniert das auch in Excel 365? Ja, die beschriebenen Methoden sind mit Excel 365 sowie anderen gängigen Versionen kompatibel.

Beliebteste Forumthreads (12 Monate)