Pam hat zwei Datenspalten. In Spalte A gibt es einfache Bezeichner wie A, B, C usw. In Spalte B gibt es eine Reihe von ganzzahligen Werten.

Sie kann die Daten nach dem Bezeichner und zweitens nach den ganzzahligen Werten sortieren. Jetzt möchte sie in Spalte C eine Formel haben, die alle ganzzahligen Werte für einen bestimmten Bezeichner verkettet. Wenn also A1: A4 alle die Kennung A enthalten, möchte sie in Zelle C1 alle Werte in B1: B4 verketten und durch Kommas teilen lassen, z. B. „11, 17, 19, 25“. Da die Anzahl der Zeilen für jeden Bezeichner unterschiedlich sein kann, ist sich Pam nicht sicher, wie die Verkettung erfolgen soll.

Der einfachste Weg, dies zu erreichen, ist die Verwendung eines Makros, das als benutzerdefinierte Funktion erstellt werden kann. Hier ist ein Beispiel:

Function CatSame(c As Range) As String     Application.Volatile     sTemp = ""

iCurCol = c.Column     If iCurCol = 3 Then         If c.Row = 1 Then             sLast = ""

Else             sLast = c.Offset(-1, -2)

End If         If c.Offset(0, -2) <> sLast Then             J = 0             Do                 sTemp = sTemp & ", " & c.Offset(J, -1)

J = J + 1             Loop While c.Offset(J, -2) = c.Offset(J - 1, -2)

sTemp = Right(sTemp, Len(sTemp) - 2)

End If     End If     CatSame = sTemp End Function

Diese Funktion verwendet im Wesentlichen einen Wert, der an sie übergeben wird (eine Zellreferenz) und überprüft, ob die Zellreferenz für Spalte C gilt. Wenn dies der Fall ist, werden Werte aus Spalte B basierend auf den Werten in Spalte A verkettet Gibt die Zeichenfolge verketteter Werte zurück, wenn sich der Wert in Spalte A von dem Wert in der Zeile darüber unterscheidet.

Angenommen, Ihre Bezeichner befinden sich in Spalte A und Ihre zu verkettenden Werte in Spalte B, können Sie Folgendes in Spalte C einfügen:

=CatSame(C1)

Kopieren Sie dies so weit wie nötig in Spalte C und Sie erhalten genau das, was Pam wollte.

Eine vielseitigere Funktion wäre eine, die ähnlich wie VLOOKUP funktioniert, aber eine verkettete Liste von Werten zurückbringt, die mit dem übereinstimmen, was Sie suchen. Betrachten Sie die folgende Funktion:

Function VLookupAll(vValue, rngAll As Range, _   iCol As Integer, Optional sSep As String = ", ")

Dim rCell As Range     Dim rng As Range     On Error GoTo ErrHandler

Application.Volatile     Set rng = Intersect(rngAll, rngAll.Columns(1))

For Each rCell In rng         If rCell.Value = vValue Then _           VLookupAll = VLookupAll & sSep & _           rCell.Offset(0, iCol).Value     Next rCell

If VLookupAll = "" Then         VLookupAll = CVErr(xlErrNA)

Else         VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))

End If ErrHandler:

If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)

End Function

Diese Funktion akzeptiert bis zu vier Argumente. Der erste ist der Wert, den Sie in Ihrer Suche abgleichen möchten. In Pams Fall wäre dies der gewünschte Bezeichner, z. B. A, B oder C. Das zweite Argument ist der Zellenbereich, in dem nach Übereinstimmungen gesucht werden soll (in diesem Fall Spalte A).

Das dritte Argument ist ein Offset (vom Bereich im zweiten Argument)

Das sind die Werte, die Sie verketten möchten. Sie können die Funktion folgendermaßen verwenden:

=VLookupAll("B",A1:A99,1)

Wenn Sie ein anderes Trennzeichen zwischen den Werten angeben möchten, können Sie dies mit dem optionalen vierten Argument tun. Im Folgenden wird beispielsweise eine Zeichenfolge zurückgegeben, bei der jeder Wert durch einen Bindestrich getrennt wird:

=VLookupAll("B",A1:A99,1,"-")

Die bisherigen Lösungen haben sich auf die Verwendung von Makros konzentriert. Der Grund dafür ist relativ einfach: Es gibt keine formelbasierte Lösung, die das tun kann, was Pam braucht. Die Verwendung verschachtelter IF-Anweisungen zur Bewertung der Inhalte in Spalte A funktioniert nicht gut, da Sie nur begrenzt in der Lage sind, IF-Anweisungen zu verschachteln.

Sie können eine Formel und ein Zwischenergebnis verwenden, wenn es Ihnen nichts ausmacht, dass sich die verketteten Werte an der letzten Instanz eines Bezeichners in Spalte A befinden. Fügen Sie diese Formel zunächst in Zelle C1 ein:

=B1

Diese Formel sollte in Zelle C2 eingehen:

=IF(A2=A1,C1 & ", " & B2, B2)

Kopieren Sie diese Formel so viele Zeilen wie nötig nach unten. Am Ende erhalten Sie eine immer längere Reihe verketteter Werte in Spalte C, wobei sich die längste in jedem Lauf in derselben Zeile befindet wie die letzte sequentielle Kennung in Spalte A. Anschließend können Sie Folgendes in alle zutreffenden Zellen der Spalte einfügen D:

=IF(LEN(C2)>LEN(C1),"",C1)

Diese Formel zeigt nur die längsten Zeichenfolgen aus Spalte C an, was Pam zunächst benötigte.

_Hinweis: _

Wenn Sie wissen möchten, wie die auf dieser Seite (oder auf einer anderen Seite der ExcelTips-Websites) beschriebenen Makros verwendet werden, habe ich eine spezielle Seite vorbereitet, die hilfreiche Informationen enthält.

Klicken Sie hier, um diese spezielle Seite in einem neuen Browser-Tab zu öffnen.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9199) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Verketten von Werten aus einer variablen Anzahl von Zellen.