Excel: Vergleichen mehrerer Spalten & Ausgabe eindeutiger Werte?
Hallo allerseits,
ich stehe vor folgender Herausforderung:
Auf einem Arbeitsblatt (Trainings-Wochenplan) gibt es pro Tag eine Spalte, die mit den Namen der jeweiligen Trainingsteilnehmer befüllt ist - sofern jeden Tag die selben Personen teilnehmen, ist noch alles unkompliziert.
Mein Problem besteht darin, dass mitunter jeden Tag Änderungen auftreten und verschiedene Teilnehmer in der jeweiligen Tagesspalte stehen ... manche sind an mehreren Tagen anwesend, andere vielleicht nur an einem Tag.
Mein Ziel besteht darin, aus den einzelnen Spalten nun jede Person nur einmalig zu erfassen und Duplikate zu ignorieren, um eine Teilnehmerliste in einer weiteren Spalte auszugeben (am besten alphabetisch sortiert).
Da dies alles dynamisch passieren soll, bin ich an einer Formellösung interessiert - ich hab' bereits gegoogelt, habe aber leider noch keine Lösung gefunden, die über den Vergleich von zwei Spalten hinausgeht ...
Vielen Dank vorab und freundliche Grüße,
Chris
@ DanKirpan & Suboptimierer:
Ich wollte Eure Lösungsvorschläge soeben ausprobieren, habe anfangs aber nicht berücksichtigt, dass zwischen den Namensspalten auch die Kurzzeichen der Teilnehmer stehen, welche nicht mitgenommen werden sollen. Außerdem gibt es diesen Tabellenblock auf der zweiten Seite nochmal (für insgesamt 12 Trainingseinheiten), was das Ganze wahrscheinlich langsam unlösbar macht :(
Ich entschuldige mich daher und habe nun einen Screenshot hochgeladen, um keine Fragen offen zu lassen - falls diese Aufgabenstellung sich irgendwie bewerkstelligen ließe, wäre ich dankbar ...
2 Antworten
Es könnte so einfach sein. Eine Liste ohne Duplikate erhältst du sehr einfach mit ZÄHLENWENN in einer Vergleichsspalte.
Das Blöde daran ist, dass du anstelle des Duplikats jedesmal eine Leerzelle ehältst.
Will man eine Liste ohne Leerzeilen, ist es etwas komplizierter.
Nehmen wir an, die Daten stehen in A1:A7.
- Erzeuge eine Hilfsspalte. B1: =1, B2: =WENN(ZÄHLENWENN($A$1:A1;A2)>0;FALSCH;ZEILE(A2)) → Das ist eine Matrixformel, deren Eingabe mit Strg+Shift+Enter abgeschlossen werden muss.
- Kopier die Formel aus B2 nach unten.
- Greife auf die Hilfsspalte mit INDEX und KKLEINSTE zu. C1: =WENNFEHLER(INDEX($A$1:$A$7;KKLEINSTE($B$1:$B$7;ZEILE(A1)));"")
- Kopier C1 nach unten.
Okey, ich sehe, du willst noch sortieren. Dann kannst du die Formeln oben wie folgt anpassen.
B1: =1+100*CODE(A1)
B2: =WENN(ZÄHLENWENN($A$1:A1;A2)>0;FALSCH;ZEILE(A2)+100*CODE(A2))
C1: =WENNFEHLER(INDEX($A$1:$A$7;REST(KKLEINSTE($B$1:$B$7;ZEILE(A1));100));"")
Die Idee: Mit Code schiebst du im Hunderterbereich den Zeichenwert des ersten Zeichens einer Zelle der Zeile voran, die mit INDEX und REST rückreferenziert werden kann.
Hallo,
in der Tat eine Herausforderung, mit zwei Hilfsspalten ist es möglich. In der ersten Hilfsspalte werden alle Tage untereinander aufgelistet:
- {=WENNFEHLER(INDEX($A$1:$D$7;REST((ZEILE(A1)-1);((MAX(WENN($A$1:$D$7<>"";ZEILE($A$1:$D$7);0))-1)))+2;(ABRUNDEN((ZEILE(A1)-1)/(MAX(WENN($A$1:$D$7<>"";ZEILE($A$1:$D$7);0))-1);0)+1));"")}
- Matrixformel, die geschweiften Klammern {} nicht mit eingeben, sondern die Eingabe über Strg+Umschalt+Enter abschließen.
- $A$1:$D$7 Tagespalten mit Überschrift in Zeile 1
REST((ZEILE(A1)-1);((MAX(WENN($A$1:$D$7<>"";ZEILE($A$1:$D$7);0))-1)))+2 zählt für die Zeile jeweils von 0 bis zur Teilnehmerzahl am meistbesuchten Tag-1, die +2 ergibt sich daraus das in meiner Tabelle der erste Teilnehmer in Zeile 2 steht. (ABRUNDEN((ZEILE(A1)-1)/(MAX(WENN($A$1:$D$7<>"";ZEILE($A$1:$D$7);0))-1);0)+1) erhöht entsprechend die Spaltennummer für die weiteren Tage.
In der zweiten Hilfsspalte wird die Sortierung vorbereitet, dazu wird ausgenutzt das man auch Texte mit einander vergleichen kann. (Je weiter vorn im Alphabet desto kleiner), nebenbei werden auch die Duplikate aussortiert:
- {=WENN(ODER(F2="";F2=0;ZÄHLENWENN($F$2:F2;F2)>1);ZEILEN($A$1:$D$7)*SPALTEN($A$1:$D$7)+1;SUMME(WENN(F2>INDIREKT("F1:F"&VERGLEICH(0;F:F;1);1);1;0)))}
- Matrixformel wieder beachten
- Spalte F ist bei mir Hilfsspalte 1
ZEILEN($A$1:$D$7)*SPALTEN($A$1:$D$7)+1 ist hierbei nur dazu da die aussortierten Einträge garantiert eine größere Zahl als der letzte erwünschte Eintrag erhalten zu lassen. INDIREKT("F1:F"&VERGLEICH(0;F:F;1);1) um zu vermeiden das man sie manuell an die Größe von Hilfsspalte 1 anpassen muss.
Aus diesen zwei Hilsspalten lassen sich die Teilnehmer sortiert ausgeben:
- =WENN(INDEX(F:F;VERGLEICH(KKLEINSTE(G:G;ZEILE(A1));G:G;0))=0;"";INDEX(F:F;VERGLEICH(KKLEINSTE(G:G;ZEILE(A1));G:G;0)))
Hallo! Ganz vielen herzlichen Dank - ich werde das mal so ausprobieren und anschließend Rückmeldung erstatten :-))
Hallo Suboptimierer, vielen Dank für Deine Lösung - ich versuche das mal und gebe anschließend Bescheid :-))