Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 10:38:52
Bedingte Formatierung per VBA / MaxPoint
25.04.2024 10:30:04
Suche mit MINWENNS und Rückgabe mehrerer Werte
25.04.2024 09:47:49
Daten aus mehreren Dateien untereinander importieren
25.04.2024 09:31:31
Bild "In Zelle platzieren"
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1476to1480
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1476to1480
1476to1480
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Neusortierung meiner Tabelle

Neusortierung meiner Tabelle
17.02.2016 20:14:22
Christian
Hallo an ihr alle,
hoffe ihr schafft es dass was ich mir wünsche, umzusetzen. Bzw. ich hoffe es hat jemand eine Idee
Die Datenquelle ist Tabelle1.
Prinzipiell geht es darum, in der ersten Spalte folgendes auszugeben, wenn ich eine Formel über 1320 Zeilen kopiere, die Inhalte von
A1, A1, A1, A1, A2, A2, A2, A2, ..., A330, A330, A330, A330
in der zweiten Spalte
H1, I1, J1, K1, H2, I2, J2, K2, ..., H330, I330, J330, K330
auszugeben.
Aber jetzt zu meinem eigentlichen Problem, wenn ich das hab suche ich eine Möglichkeit, das Ganze zeilenweise, nicht spaltenweise alphabetisch zu sortieren.
also wenn z.B. in A1 die Stadt Köln steht, in H1 die Stadt Bonn, soll nach diesem Sortieren zuerst die Stadt Bonn da stehen.
Hat da jemand eine Idee?
Danke und Gruß
Christian
https://www.herber.de/bbs/user/103657.xlsx

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Neusortierung meiner Tabelle
18.02.2016 00:09:32
Piet
Hallo Christian
bei der Aufgabe ist mir noch so einiges unklar ...
Es heisst, es soll eine Formel über 1320 Zeilen kopiert werden - in die 2. Spalte!
H, I, J, K sind aber 4 Spalten und dort sehe ich die Staedte wild durcheinander stehen.
Beim Sortieren von z.B. mehreren Spalten gibt es den Sortierbezug. Er sagt welche Spalte
den Vorrang vor den anderen Spalten hat. Was soll den hier genau von wo nach wo kopiert
werden, und welche Spalten sollen anschliessend sortiert werden? Ist mir noch unklar....
mfg Piet

AW: Neusortierung meiner Tabelle
18.02.2016 07:01:52
Christian
Hallo Pete,
erstmal danke für die Rückmeldung.
In der 2. Spalte soll untereinander stehen:
Maastricht, Bonn, Lüttich, Köln, Leer, Randers, Viborg, Leer, Crailsheim, Augsburg, Ulm, Ansbach, usw.
Wobei es mir egal ist was mit den Leerzellen passiert, habe sowieso im Anschluss vor, die betroffenen Zeilen in der neuen Tabelle zu löschen.
Beim Sortieren war das so gemeint, es stehen jetzt jeweils 2 Städte nebeneinander in den neuen Spalten. Ich will erreichen, dass die die zuerst im Alphabet kommt, links steht, die die als zweites im Alphabet kommt rechts.
Schau mal ganz nach unten, die 7. letzte Zeile der neuen Tabelle müsste dann Zürich und Feldkirch sein.
Feldkirch kommt im Alphabet vor Zürich also soll irgendwas diese beiden Einträge vertauschen.
Genauso wie 3 Zeilen weiter oben Znaim und St. Pölten, auch hier steht St. Pölten im Alphabet vor Znaim, also soll der Mechanismus auch das tauschen.
Gruß
Christian
PS: Bin heut den ganzen Tag unterwegs, werd erst wahrscheinlich morgen nach der Arbeit wieder antworten können.

Anzeige
AW: die 2. Spalte mit INDEX() und AGGREGAT() ...
18.02.2016 09:56:19
...
Hallo Christian,
... folgende Matrixfunktion(alität)sFormel in B1: =WENNFEHLER(INDEX(H:K;KÜRZEN(AGGREGAT(15;6;(ZEILE(A$1:A$325)*10+SPALTE(A1:D1))/(H$1:K$325>0); _ ZEILE(A1))/10;); REST(AGGREGAT(15;6;(ZEILE(A$1:A$325)*10+SPALTE(A1:D1))/(H$1:K$325>0);ZEILE(A1));10));"") ziehend nach unten kopiert ergibt die Auflistung der Stadtnamen aus Spalte H:K ohne dortigen Leerzellen.
Was Du aber sonst noch meinst, ist mir immer noch unklar.
In Spalte A hast Du doch schon Stadtnamen stehen und da gibt es in der 7.letzten Zelle auch kein Zürich etc. Dies steht in der 2.letzten Zelle ...
Was strebst Du an?
Gruß Werner
.. , - ...

Anzeige
AW: die 2. Spalte mit INDEX() und AGGREGAT() ...
18.02.2016 16:24:30
Christian
Hallo Werner,
ich versuche es mal ganz anders zu erklären.
Stell die Spalte A als Auflistung von 330 Städten vor und die Spalten H bis K als Auflistung von bis zu 4 Städten die von der jeweiligen Stadt aus zu erreichen sind.
Mein Ziel ist eine Auflistung aller Verbindungen.
Und dann im zweiten Schritt jede einzelne Verbindung so sortiert, dass die alphabetisch erste Stadt links steht und die alphabetisch zweite Stadt rechts.
Gruß
Christian

AW: Text sortieren mit geschachtelt. AGGREGAT()...
18.02.2016 17:28:52
...
Hallo Christian,
... und unter zusätzlichem Einsatz von ZÄHLENWENN() und das wiederum nur mit einer Matrixfunktion(alität)sformel.
Formel nach rechts und unten ziehend kopieren:
 ABCDEFGHIJK
1AachenBonnKölnLüttichMaastricht  MaastrichtBonnLüttichKöln
2AalborgRandersViborg     RandersViborg 
3AalenAnsbachAugsburgCrailsheimUlm  CrailsheimAugsburgUlmAnsbach
4AarhusKoldingOdenseRanders   RandersOdenseKolding 
5AbbevilleAmiensBoulogne-sur-MerCalaisDieppe  Boulogne-sur-MerAmiensDieppeCalais
6AlkmaarAmsterdamDen HaagLeeuwarden    AmsterdamDen HaagLeeuwarden
7AlmereAmsterdamApeldoornMeppelUtrecht  AmsterdamApeldoornUtrechtMeppel
8AmiensCompiègneLilleRouen    CompiègneRouenLille
9AmsterdamDen HaagMeppel      Den HaagMeppel
10AmstettenLinzSt. PöltenSteyr   Linz SteyrSt. Pölten

Formeln der Tabelle
ZelleFormel
B1=INDEX(1:1;AGGREGAT(14;6;SPALTE($H1:$K1)/(ZÄHLENWENN($H1:$K1;">="&$H1:$K1)=AGGREGAT(14;6;ZÄHLENWENN($H1:$K1;">="&$H1:$K1); SPALTE(A1))); 1))&""


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: Text sortieren mit geschachtelt. AGGREGAT()...
18.02.2016 20:13:05
Christian
Hallo Werner,
danke für die Mühe, aber das ist auch nicht das was ich gesucht habe, ich glaube sogar das was du gemacht hast, ist viel komplizierter als das was ich gesucht habe. Du musst das auch nicht alles in eine Formel packen, Hilfsspalten kann ich ja löschen wenn ich in den edgültigen Spalten die Werte einfüge.
Ich dachte an sowas:
2 Spalten
Aachen Maastricht
Aachen Bonn
Aachen Lüttich
Aachen Köln
Aalborg Randers
Aalborg Viborg
Aalen Crailsheim
Aalen Augsburg
usw.
bis zuletzt
Zürich Feldkirch
und dann im zweiten Schritt die Paare jeweils in alphabetische Reihenfolge bringen, z.B. Zürich und Feldkirch umdrehen, weil F vor Z im Alphabet kommt.
Gruß
Christian

Anzeige
AW: dann mit einer weiteren AGGREGAT()-Formel ...
19.02.2016 09:08:38
...
Hallo Christian,
... wenn Du es so willst, betrachte ich jetzt Deine Spalte A als Hilfsspalte und die Spalten B und C auch.
Die Formel in C1 hatte ich Dir ja schon gestern Morgen im Prinzip bereitgestellt, da nur für Spalte B und einem kleinerem Datenbereich. C1-Formel weit genug (im Beispiel bis C825) nach unten ziehend kopieren. Ebenso die neue Formel B1.
Dein Zielergebnis bekommst Du, wenn Du die Formeln der Spalte B:C dann einfach durch Ihre Werte ersetzt und dann diese entsprechend sortierst.
 ABCDEFGHIJK
1AachenAachenMaastricht    MaastrichtBonnLüttichKöln
2AalborgAachenBonn     RandersViborg 
3AalenAachenLüttich    CrailsheimAugsburgUlmAnsbach
4AarhusAachenKöln    RandersOdenseKolding 
5AbbevilleAalborgRanders    Boulogne-sur-MerAmiensDieppeCalais
6AlkmaarAalborgViborg     AmsterdamDen HaagLeeuwarden
7AlmereAalenCrailsheim    AmsterdamApeldoornUtrechtMeppel
8AmiensAalenAugsburg     CompiègneRouenLille
9AmsterdamAalenUlm      Den HaagMeppel
10AmstettenAalenAnsbach    Linz SteyrSt. Pölten

Formeln der Tabelle
ZelleFormel
B1=WENN(C1="";"";INDEX(A:A;AGGREGAT(15;6;ZEILE(A$1:A$999)/(H$1:K$333=C1); ZÄHLENWENN(C$1:C1;C1))))
C1=WENNFEHLER(INDEX(H:K;KÜRZEN(AGGREGAT(15;6;(ZEILE(A$1:A$333)*10+SPALTE(A1:D1))/(H$1:K$3333>0); ZEILE(A1))/10;); REST(AGGREGAT(15;6;(ZEILE(A$1:A$3333)*10+SPALTE(A1:D1))/(H$1:K$3333>0); ZEILE(A1)); 10)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: dann mit einer weiteren AGGREGAT()-Formel ...
19.02.2016 11:20:13
Christian
Hallo Werner,
ja das funktioniert soweit, danke.
Aber noch eine Frage, gibt es zum sortieren auch einen einfacheren Weg als transponieren und dann jede Spalte einzeln sortieren?
Gruß
Christian

AW: momentan mir unklar, was Du jetzt meinst ...
19.02.2016 12:05:30
...
Hallo Christian,
... die Ergebnisspalten B:C müssen doch nicht transponiert werden.
Nach dem Austauschen der Formeln durch ihre Ergebniswerte, kannst Du doch diese in einem Sortiervorgang wie gewünscht sortieren lassen.
Dir war doch eine Hilfsspaltenlösung Recht. Wenn die Ergebnisse in Spalte C gleich sortiert ausgegeben werden soll, müsste ich nochmal nachdenken. Auf jeden Fall wird die Formel dann noch komplexer.
Gruß Werner
.. , - ...

Anzeige
AW: also doch ohne transponieren .....
19.02.2016 12:46:48
...
Hallo Christian,
... und geht aber nur, wenn Du es nachträglich auf Basis der ermittelten Spalte C vorgenommen hast. Oder? Diese formelbasierende Sortierung kann man mittlerweile aber auch auf Basis von AGGREGAT() ohne {} vornehmen. Aber nun hast Du ja schon Dein Ergebnis.
Gruß Werner
.. , - ...

AW: also doch ohne transponieren .....
19.02.2016 13:06:36
Christian
Hallo Werner,
bin hingegangen und habs transponiert und dann die in der Formel: 

{=INDEX(A$1:A$10;VERGLEICH(KKLEINSTE(ZÄHLENWENN(A$1:A$10;"
die 10 in 2 geändert und dann bis Spalte AAschlagmichtot kopiert.
und dann wieder zurück transponiert.
Gruß
Christian
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige