Hallo Hilfesuchende,
falls es jemanden interessiert, wie ich hier nach viel Probieren meine Formeln zum Daten nach Art und dann alphabetisch Sortieren und zum lückenlosen Zusammenführen
verschiedener Spalten gelöste habe, gebe ich Euch hier nun eine kurze Beschreibung der Vorgehensweise und die entsprechenden Formeln dazu.
(Ich weiß aus eigener
Erfahrung, wie lange man suchen muß, um bei komplizierteren Fragestellungen den richtigen Formelansatz hinzubekommen)
Die Problematik habe ich oben bereits beschrieben. Einzig dazu anzumerken ist, dass die Kundenart innerhalb des Namens unterschieden wird ("Labor XY" oder
"Dr. Z") Die Lösung ist folgende:
=WENN(ODER(INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1)="";LINKS(INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1);2)="Dr")=1;"";
INDEX($A$1:$J$100;REST(ZEILE()-1;100)+1;(ZEILE()-1)/100+1))
Hier werden zunächst alle Einträge der Tabelle in eine Spalte geschrieben (inklusive Leerzeilen innerhalb des Tabbellenbereiches) und daraus wird noch die Kundenart herausgefiltert.
--> LINKS(Zelle;die ersten zwei Buchstaben)="Dr". Selbiges habe ich für alle Kundenarten gemacht.
{=WENN(ZEILE()>ANZAHL2($A$1:$J$100);"";INDEX($K$1:$K$1000;KKLEINSTE(WENN($K$1:$K$1000<>"";ZEILE($O$1:$O$1000));ZEILE())))}
Dann kam die Matrixformel, mithilfe derer die Leerzeilen innerhalb der neuen Spalten gelöscht wurden.
=WENN(ISTFEHLER(O1)=1;"";WERT(VERKETTEN(CODE(KLEIN(TEIL(O1;7;1)));",";CODE(KLEIN(TEIL(O1;8;1)))))+ZEILE()*0,0000001)
Dann habe ich für die alphabetische Sortierung die ersten beiden Namensbuchstaben mit der Funktion CODE(KLEIN(TEIL(Zelle;ab Buchstabe X;1 Buchstabe)))in Zahlen
umgewandelt und mit WERT(VERKETTEN(Code 1;","Code 2)) erst mit einem Komma in der Mitte verkettet und dann in einen zählbaren Wert zurückverwandelt. Wichtig ist hier
die Addierung mit einer extrem kleinen von Zeile zu Zeile minimal unterschiedlichen Zahl hier +ZEILE()*0,0000001, da sonst Probleme auftreten, wenn 2 Namen identisch
sind! Dadurch könnte man die Rangfolge der Sortierung nicht eindeutig festlegen!
=WENN(ISTFEHLER(O1)=1;"";RANG(M1;M$1:M$1000;1))
Dann werden diese Zahlen, bei mir beispielsweise 122,2280007 anhand der Größe in eine Rangfolge gebracht. Die Rangfolge sollte in der Spalte links von der Matrixformel
stehen, damit der SVERWEIS gleich auch funktioniert.
=WENN(ISTFEHLER(R1)=1;"";SVERWEIS(ZEILE();Q$1:R$1000;2;0))
Nun haben wir die einzelnen Kundenarten alphabetisch sortiert in einer Spalte vorliegen.
=WENN(INDEX($S$1:$T$1000;REST(ZEILE()-1;1000)+1;(ZEILE()-1)/1000+1)="";"";INDEX($S$1:$T$1000;REST(ZEILE()-1;1000)+1;(ZEILE()-1)/1000+1))
Jetzt werden die Kundenarten zusammengesetzt (wieder mit Leerzeichen dazwischen)
{=WENN(ZEILE()>ANZAHL2($S$1:$T$1000);"";INDEX($U$1:$U$2000;KKLEINSTE(WENN($U$1:$U$2000<>"";ZEILE($V$1:$V$2000));ZEILE())))}
und mit derselben Matrixformel Leerzeichen-frei geschrieben.
=WENN(ISTFEHLER(V1)=1;"";V1)
Nun noch den "#Wert!"-Fehler beheben und das Ganze sauber in einer Ergebnisspalte ausgeben.
=WENN(W1<>"";WENN(ISTNV(SVERWEIS(W1;Y$1:Z$100;2;0))=0;SVERWEIS(W1;Y$1:Z$100;2;0);WENN(ISTNV(SVERWEIS(W1;AA$1:AB$100;2;0))=0;SVERWEIS(W1;AA$1:AB$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AC$1:AD$100;2;0))=0;SVERWEIS(W1;AC$1:AD$100;2;0);WENN(ISTNV(SVERWEIS(W1;AE$1:AF$100;2;0))=0;SVERWEIS(W1;AE$1:AF$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AG$1:AH$100;2;0))=0;SVERWEIS(W1;AG$1:AH$100;2;0);WENN(ISTNV(SVERWEIS(W1;AI$1:AJ$100;2;0))=0;SVERWEIS(W1;AI$1:AJ$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AK$1:AL$100;2;0))=0;SVERWEIS(W1;AK$1:AL$100;2;0);WENN(ISTNV(SVERWEIS(W1;AM$1:AN$100;2;0))=0;SVERWEIS(W1;AM$1:AN$100;2;0);
WENN(ISTNV(SVERWEIS(W1;AO$1:AP$100;2;0))=0;SVERWEIS(W1;AO$1:AP$100;2;0);WENN(ISTNV(SVERWEIS(W1;AQ$1:AR$100;2;0))=0;SVERWEIS(W1;AQ$1:AR$100;2;0)))))))))));"")
Meine Mitarbeiterdaten habe ich dann mit einer einfachen aber ziemlich langen SVERWEIS-Formel dazugestellt.
Also ich finde, dass ich mich als Anfänger in Sachen Formeln-schreiben ganz gut geschlagen habe. Man braucht ein gutes Forum (!) -- ich kann da übrigens eines empfehlen#
-- und ein wenig Phantasie!
Vielen Dank nochmals an alle Forennutzer und all die fleißigen und geduldigen "Besserwisser", die es uns Anfängern doch sehr viel leichter machen, uns zurechtzufinden!
Herzliche Grüße aus dem Frankenlande, Zahnerer