Mehrere Spalten - Vergleichsproblem

[Hammsi]

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:

[BoskoBiati]

Hi,

Code:

=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)));"")

[BoskoBiati]

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.

[Hammsi]

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?

[GMG-CC]

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.

[Gast 123]

Hallo

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

[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

[Jockel]

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

[BoskoBiati]

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

[Jockel]

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