Gibt es in Excel eine Excel-Formel, mit der ich Spalte B (in meinem Beispiel unten) auffüllen kann, die die letzte Zeilennummer vor der aktuellen Zeile zurückgibt, die eine bestimmte Zeichenfolge enthält?
Die Daten sind nicht zusammenhängend.
Die Daten können beispielsweise sein:
| A (value) B (prev recent instance)
----------------------------------------------
1 | Mike
2 | John
3 | Mike 1
4 | Tony
5 | Mike 3
6 | John 2
Ich möchte es dann erweitern, sodass in Spalte C die früheste Instanz angezeigt wird.
Es scheint dieser Frage sehr ähnlich zu sein: Welche Excel-Formel gibt mir die letzte Zeilennummer einer Zelle innerhalb eines Bereichs zurück, der bestimmte Daten enthält?, allerdings erfordern diese Frage und Antwort, dass die Daten zusammenhängend sind.
Bearbeiten: Ich hätte die Antwort gerne in Form einer Arbeitsblattformel. Wenn Sie jedoch der Meinung sind, dass eine Implementierung über VBA geeigneter ist, nehmen Sie das bitte ebenfalls mit den Vor- und Nachteilen der einzelnen Ansätze in Ihre Antwort auf.
Antwort1
Um die früheste Instanz zu finden, verwenden Sie die folgende Formel: -
=LOOKUP(2,1/(A:A=A2),ROW(A:A))
Zum besseren Verständnis ist unten der Screenshot
Antwort2
Angenommen, Ihre Daten beginnen bei A1, versuchen Sie diese "Array-Formel" in B2
=IF(COUNTIF(A$1:A1,A2),MATCH(2,1/(A$1:A1=A2)),"")
mit STRG+UMSCHALT+EINGABE bestätigt und nach unten kopiert
oder Sie können diese Nicht-Array-Version verwenden
=IF(COUNTIF(A$1:A1,A2),MATCH(2,INDEX(1/(A$1:A1=A2),0)),"")
In Excel 2007 oder höher können Sie mit IFERROR vereinfachen, d. h. die Array-Version wird
=IFERROR(MATCH(2,1/(A$1:A1=A2)),"")
Versuchen Sie für die Zeile „1. Instanz“ in Spalte C diese Formel in C1, die Sie nach unten kopiert haben.
=MATCH(A1,A$1:A1,0)
Hinweis: Die Formeln geben nicht unbedingt „Zeilennummern“ zurück, sondern die relativen Zeilennummern für Ihre Daten, d. h. Zeile 1 Ihrer Daten gibt 1 zurück usw.