Bedingtes Verketten von Zellinhalten über mehrere Zeilen hinweg

Question

Ich habe ein Arbeitsblatt mit Tausenden von Zeilen, die eine eindeutige ID Aund drei verschiedene Fehlercodes enthalten M. Jede einzelne ID kann 1, 2 oder 3 Fehler enthalten. Sie stehen immer in derselben Reihenfolge.

Ich habe die gewünschte Ausgabe per Hand eingegeben N. Ich habe eine Formel wie diese in Spalten O, P, Qmit dem Text des Fehlers geschrieben:

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

Ich habe so etwas in versucht, Raber es funktioniert aus offensichtlichen Gründen nicht richtig. Gibt es eine Möglichkeit, dies so zu machen, dass es Rso aussieht N?

=CONCATENATE(O42,"/", P42,"/",Q42)

Wenn dies mit einer Excel-Formel nicht möglich ist, gibt es vielleicht eine VBA-Möglichkeit?

Answer 1

OK, ich musste mir den Kopf zerbrechen, aber ich habe es verstanden:

Spalten O, P& Qwie Sie sie haben, mit den Titeln Brand, Product& OEM. Wandeln Sie das Ganze in eine Tabelle um mit Ctrl- T(nicht erforderlich, aber praktisch, und meine Spalte Rist darauf angewiesen, aber Sie können Spaltenverweise verwenden, wenn Sie möchten)

Spalte R:

=IF(LEN([Brand])>0,[Brand],IF(LEN([Product])>0,[Product],IF(LEN([OEM])>0,[OEM],"")))

Spalte S:

=IF(A2=A4,F2&"/"&F3&"/"&F4,IF(A2=A1,"",IF(A2=A3,F2&"/"&F3,F2)))

Leider scheint die einzige Möglichkeit, Tabellenreferenzen auf eine andere Zeile zu verwenden Offset, die Verwendung von zu sein. Um es einfacher zu halten, bin ich also zu Zellreferenzen zurückgekehrt. Das negiert zwar den coolen/praktischen Faktor, das Ganze überhaupt in eine Tabelle umzuwandeln, aber egal ...

Und... hier ist ein Bild, wie es aussieht:

Answer 2

OIch würde mit der Optimierung Ihrer PFormeln beginnen Q. Sie haben derzeit

=IF(IFERROR(SEARCH("Brand is not valid", M42), "") <> "", "Brand", "")

IFERRORist eine großartige Funktion zum Anzeigen einer bereinigten Version eines berechneten Werts, der ein Fehlercode sein könnte; ich verwende sie und empfehle sie in Antworten auf Super User häufig. Wie Sie wahrscheinlich wissen,

• IFERROR(calculated_value, default_value)

Kurzform für

• IF(ISERROR(calculated_value), default_value, calculated_value)

Aber verwenden IFERROR, um eine bereinigte Version eines Wertes zu erstellen und dann diesen Wert testen, um etwas bedingt zu tun ist eine unnötig umständliche Art zu verwenden IFERROR. Die obige Formel kann vereinfacht werden zu

=IF(ISERROR(SEARCH("Brand is not valid", M42)), "", "Brand")

Und wie Sie sicher wissen, SEARCH("Brand is not valid", M42) gibt es Tests, um zu sehen, obM42 enthält Brand is not valid. Aber solange Column Mnur Ihre drei Fehlerzeichenfolgen enthalten kann, kann dies verkürzt werden auf

=IF(ISERROR(SEARCH("Brand", M42)), "", "Brand")

oder vereinfacht zu

=IF(M42 = "Brand is not valid", "Brand", "")

---

OK, jetzt mache ich die Formeln O, P, und Qetwas komplizierter:

• O42→=IF($A42=$A41, O41, "") & IF(ISERROR(SEARCH("Brand", $M42)), "", "Brand")
• P42→=IF($A42=$A41, P41, "") & IF(ISERROR(SEARCH("Product", $M42)), "", "Product")
• Q42→=IF($A42=$A41, Q41, "") & IF(ISERROR(SEARCH("OEM", $M42)), "", "OEM")

Die Formel für O42lautet:

Wenn dies die zweite oder dritte Zeile für diese ID ist (Spalte A), sehen Sie sich die Zelle darüber an (also die Spaltenzelle Ofür die vorherige Zeile), um zu sehen, ob wir bereits festgestellt haben, dass dieses Ding eine ungültige Marke hat. Sehen Sie sich auch die Spalte Mfür diese Zeile an, um zu sehen, ob sie ist Brand is not valid. Verketten Sie dann die Ergebnisse.

Da eine eindeutige ID nie zweimal mit demselben Fehler aufgeführt wird (richtig?), werden diese beiden Teilergebnisse nie beide nicht leer sein, sodass hier im Wesentlichen ein „ODER“ ausgeführt wird:

Zeigen Sie einen Wert an, Brandwenn diese Zeile ODER eine der vorherigen Zeilen für diese ID den Fehler „ungültige Marke“ enthält.

Dies hat den Effekt O, die Werte P, , und Qfür jede ID in die letzte Zeile zu ziehen:

Beachten Sie, dass in den Zeilen 41, 44, 47 und 49 jeweils die Kurzformen aller Fehler angezeigt werden, die für ihre jeweiligen IDs in den Spalten O, P, und gelten Q.

Ich habe Column Rgenauso definiert wie Sie. SieheErstellen Sie eine kommagetrennte Liste der Zellinhalte ohne Leerzeichen für Techniken zum Entfernen der unerwünschten Schrägstriche.

Wenn die gewünschte Verkettung nur in den Zeilen 41, 44, 47 und 49 ausreicht, sind Sie fertig. Andernfalls definieren Sie N42als

=IF($A22=$A23, N23, R22)

oder

=IF($A22<>$A23, R22, N23)

Dies ist fast genau der gleiche Trick, den ich in den Spalten O, P, und verwendet habe Q, allerdings in die entgegengesetzte Richtung:

Wenn dies die letzte Zeile für diese ID ist (also Zeile 41, 44, 47 oder 49), verwenden Sie die Verkettung der Werte aus dieser Zeile (die die vollständige Sammlung der Fehlercodes für diese ID darstellt). Andernfalls sehen Sie sich die Zelle darunter an (also die Spaltenzelle Nfür die nächste Zeile), die die richtige Antwort enthält.

Mit anderen Worten: Die gewünschten Werte sickern für jede ID bis in die erste Zeile durch.