Liebe Excelcrowd,
trotz längerer Recherche konnte ich zu meinem Problem noch keine Lösung finden ... vielleicht wisst ihr Rat. Anbei eine Datei, wo ich gerne in Spalte D jene Namen von Spalte A ausgeben würde, die auch nur in dieser Spalte vorkommen (also weder in B noch in C vertreten sind). Im Idealfalls sollte dieser Vergleich auch für Spalte B und C funktionieren. Die Formel für Namen, die in allen drei Spalten vorkommen, lautet glaub ich folgendermaßen:
WENNFEHLER(INDEX(A:A;ZEILE(A2)*(VERGLEICH(A2;B:B;0)*VERGLEICH(A2;C:C;0)>0));"")
Und auch wenn's darum ginge, zwei Spalten auf Unterschiede vergleichen, wäre kein Problem. Aber einfache Vorkommen bei mehreren Spalten zu finden, scheint mir momentan noch ein Ding der Unmöglichkeit ...
Pls :69:
Hi,
hier auch für alle Spalten. Da wäre eine Hilfsspalte evtl. hilfreich:
| | A | B | C | D | E |
| 1 | | | | Name nur 1x | |
| 2 | Hans | Peter | Susi | Hans | Hans |
| 3 | Max | Max | Ralf | Ingrid | Ingrid |
| 4 | Ingrid | Susanne | Susanne | Alex | Alex |
| 5 | Alex | Paula | Peter | Klaus | Klaus |
| 6 | Klaus | Max | Paula | | Hannes |
| 7 | Paula | Hannes | Max | | Susi |
| Zelle | Formel |
| D2 | =WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));"") |
| E2 | {=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)/(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))));WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$B$7;$C$2:$C$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))-SUMME((ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)*(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0))));"")))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
Vielen Dank, funktioniert. Ist aber doch etwas komplizierter als erhofft :s Das Problem dabei ist, dass sich Excel aufhängt, wenn ich über 2000 Zeilen und mehrere Spalten habe. Fürchte, in solchen Fällen geht das dann nur über ein Makro, richtig?
Moin,
offensichtlich hast du doch nicht ein so antiquiertes Office/Excel wie angegeben (XP/2002), denn sonst würde AGGREGAT nicht erkannt werden.
Wenn du Excel >=
2010 hast, dann wird das bei den (für Excel sehr wenigen) Zeilen mit Power Query ->
http://www.excel-ist-sexy.de/power-query-das-add-in/ funktionieren.
Hallo
Zitat:geht das dann nur über ein Makro, richtig?
Korrektr Frage, höfliche Antwort von mir. Probier bitte die Beispieldatei aus, dann weisst du ob es funktioniert.
Bei den wenigen Beispieln hat es geklappt, bleibt offen wie gut es mit 2000 Daten klappt. I dont now.
Der Vorteil, es ist ein Universal Makro, das heisst, oben vor Sub stehen zwei Const Anweisungen für LastSpalte + ZielSpalte
Dort kannst du selbst den Tabellenbereich festleghen, denn das Makro ist nicht auf A-C begrenzt. Du kannst die Spalten beliebig erweitern.
Dazu muss natürlich auch die ZielSpalte für das Ergebnis neu festgelegt werden. Die ist auch frei waehlbar. (darf sich nicht überschneiden)
Mir ist aufgefalledn das nach dem Sortieren manche Namen doppelt sind, weil am Ende ein Space steht. Deshalb das zweite Makro damit man diese Space vor dem auflisten löschen kann. Ich denke das klaert eine menge Fragen. Rückmeldung würde mich freuen.
mfg Gast 123
Moin Günther
ich hatte deine Lösung noch garnicht gesehen als ich meine ins Forum stellte.
Nun ja, jetzt hat er zwei Lösungen, und die freie Auswahl ...
herzliche Grüsse von Gast 123
(16.05.2017, 10:59)BoskoBiati schrieb: [ -> ]Hi,
hier auch für alle Spalten. Da wäre eine Hilfsspalte evtl. hilfreich:
| | A | B | C | D | E |
| 1 | | | | Name nur 1x | |
| 2 | Hans | Peter | Susi | Hans | Hans |
| 3 | Max | Max | Ralf | Ingrid | Ingrid |
| 4 | Ingrid | Susanne | Susanne | Alex | Alex |
| 5 | Alex | Paula | Peter | Klaus | Klaus |
| 6 | Klaus | Max | Paula | | Hannes |
| 7 | Paula | Hannes | Max | | Susi |
| Zelle | Formel |
| D2 | =WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));"") |
| E2 | {=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)/(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))));WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$B$7;$C$2:$C$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))-SUMME((ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)*(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0))));"")))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
Hallo Edgar, die Formel geht kürzer und fehlerfrei und
ohne Strg+Umsch+Enter. Bei deiner Variante fehlen eine Reihe von Namen..:
Code:
=WENNFEHLER(WENNFEHLER(WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A32)/(($A$1:A33<>"")*(ZÄHLENWENN(D$1:D1;A$1:A33)=0));1));INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A32)/(($B$1:B33<>"")*(ZÄHLENWENN(D$1:D1;B$1:B33)=0));1)));INDEX(C:C;AGGREGAT(15;6;ZEILE(A$1:A32)/(($C$1:C33<>"")*(ZÄHLENWENN(D$1:D1;C$1:C33)=0));1)));"")
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| | A | B | C | D | E |
| 1 | | | | Name nur 1x | |
| 2 | Hans | Peter | Susi | Hans | Hans |
| 3 | Max | Max | Ralf | Max | Ingrid |
| 4 | Ingrid | Susanne | Susanne | Ingrid | Alex |
| 5 | Alex | Paula | Peter | Alex | Klaus |
| 6 | Klaus | Max | Paula | Klaus | Hannes |
| 7 | Paula | Hannes | Max | Paula | Susi |
| 8 | | | | Peter | Ralf |
| 9 | | | | Susanne | |
| 10 | | | | Hannes | |
| 11 | | | | Susi | |
| 12 | | | | Ralf | |
| Zelle | Formel |
| D2 | =WENNFEHLER(WENNFEHLER(WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A32)/(($A$1:A33<>"")*(ZÄHLENWENN(D$1:D1;A$1:A33)=0));1));INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A32)/(($B$1:B33<>"")*(ZÄHLENWENN(D$1:D1;B$1:B33)=0));1)));INDEX(C:C;AGGREGAT(15;6;ZEILE(A$1:A32)/(($C$1:C33<>"")*(ZÄHLENWENN(D$1:D1;C$1:C33)=0));1)));"") |
| E2 | {=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)/(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))));WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$B$7;$C$2:$C$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))-SUMME((ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)*(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0))));"")))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |
Hallo Jörg,
ich habe, wie es auch Gast123 schon bemerkt hat, die Leerzeichen entfernt, dann passt mein Ergebnis auch!
| | A | B | C | D | E | | 1 | | | | Edgar | Jörg | | 2 | Hans | Peter | Susi | Hans | Hans | | 3 | Max | Max | Ralf | Ingrid | Max | | 4 | Ingrid | Susanne | Susanne | Alex | Max | | 5 | Alex | Paula | Peter | Klaus | Max | | 6 | Klaus | Max | Paula | Hannes | Max | | 7 | Paula | Hannes | Max | Susi | Max | | 8 | | | | Ralf | Max | | 9 | | | | | Max |
|
| Zelle | Formel | | D2 | {=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)/(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))));WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$B$7;$C$2:$C$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))-SUMME((ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)*(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0))));"")))} | | E2 | =WENNFEHLER(WENNFEHLER(WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A32)/(($A$1:A33<>"")*(ZÄHLENWENN(D$1:D1;A$1:A33)=0));1));INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A32)/(($B$1:B33<>"")*(ZÄHLENWENN(D$1:D1;B$1:B33)=0));1)));INDEX(C:C;AGGREGAT(15;6;ZEILE(A$1:A32)/(($C$1:C33<>"")*(ZÄHLENWENN(D$1:D1;C$1:C33)=0));1)));"") |
| | Achtung, Matrixformel enthalten! | | Die geschweiften Klammern{} werden nicht eingegeben. | | Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
|
| Zelle | bedingte Formatierung... | Format | | A2 | 1: =ZÄHLENWENN($A$2:$C$7;A2)=1 | abc |
|
Hallo Edgar, sorry, dass ich widerspreche, dein Ergebnis bleibt... ... habe gerade nochmal die O-Datei runter geladen (wiederholt - danach sofort die Leerzeichen entfernt) dann die gepostete Formel reinkopiert --> Ergebnis..:
| Arbeitsblatt mit dem Namen 'Tabelle1 (2)' |
| | A | B | C | D | E |
| 1 | | | | Name nur 1x | |
| 2 | Hans | Peter | Susi | Hans | Hans |
| 3 | Max | Max | Ralf | Max | Ingrid |
| 4 | Ingrid | Susanne | Susanne | Ingrid | Alex |
| 5 | Alex | Paula | Peter | Alex | Klaus |
| 6 | Klaus | Max | Paula | Klaus | Hannes |
| 7 | Paula | Hannes | Max | Paula | Susi |
| 8 | | | | Peter | Ralf |
| 9 | | | | Susanne | |
| 10 | | | | Hannes | |
| 11 | | | | Susi | |
| 12 | | | | Ralf | |
| 13 | | | | | |
| Zelle | Formel |
| D2 | =WENNFEHLER(WENNFEHLER(WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A32)/(($A$1:A33<>"")*(ZÄHLENWENN(D$1:D1;A$1:A33)=0));1));INDEX(B:B;AGGREGAT(15;6;ZEILE(A$1:A32)/(($B$1:B33<>"")*(ZÄHLENWENN(D$1:D1;B$1:B33)=0));1)));INDEX(C:C;AGGREGAT(15;6;ZEILE(A$1:A32)/(($C$1:C33<>"")*(ZÄHLENWENN(D$1:D1;C$1:C33)=0));1)));"") |
| E2 | {=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7)=0);ZEILE(A1)));WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)/(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))));WENNFEHLER(INDEX(C:C;AGGREGAT(15;6;ZEILE($A$2:$A$7)/(ZÄHLENWENN($A$2:$B$7;$C$2:$C$7)=0);ZEILE(A1)+1-SUMME(ZÄHLENWENN($B$2:$C$7;$A$2:$A$7))-SUMME((ZÄHLENWENN($A$2:$A$7;$B$2:$B$7)=0)*(ZÄHLENWENN($C$2:$C$7;$B$2:$B$7)=0))));"")))} |
| Achtung, Matrixformel enthalten! |
| Die geschweiften Klammern{} werden nicht eingegeben. |
| Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine. |
| Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg |