Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
23.04.2024 17:00:22
Summe und Ausgabe
23.04.2024 16:27:34
Excel Starthinweis - Arbeitsmappenleistung überprüfen ...
23.04.2024 16:15:16
Mittels VBA vordefinierte Kommentare in Zellen einfügen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1864to1868
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
1864to1868
1864to1868
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Auflistung von Werten

Auflistung von Werten
11.01.2022 16:06:53
Werten
Hallo Excelfreunde!
Folgende Frage: Habe Werte (Zahlen und Buchstaben) in einer Spalte (I11 bis I250) welche ich in einer Zeile aufgelistet haben möchte (I7 bis X7). Wobei die Zahlen bei I7 in aufsteigender Reihenfolge und die Werte von X7 in Absteigender Reihenfolge geschrieben werden sollen. Leere Zellen sollen ignoriert werden. Ich weis nicht wie ich das anstellen soll.
Habe eine Beispieldatei in den Anhang gestellt.
https://www.herber.de/bbs/user/150330.xlsx
Besten Dank für jede Hilfe!
Florian

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Auflistung von Werten
11.01.2022 16:29:07
Werten
Hi Florian
Mal wieder eine Lösung mittels Power-Query:
https://www.herber.de/bbs/user/150331.xlsx
Zahlen und Texte sind gemäss deinem Beispiel beide aufsteigend sortiert.
cu
Chris
AW: Auflistung von Werten
11.01.2022 16:59:29
Werten
Hi Chris!
Besten Dank für die schnelle Hilfe.
Wenn ich das richtig sehe, dann hilft mir deine Lösung nur für den Moment. Sie sollte allerdings automatisch bei jedem Eintrag in den Bereich I11:I250 erfolgen und nicht jedes mal händisch angeschmissen werden müssen sobald ein Wert ergänzt, bzw. adaptiert wird.
Des Weiteren bräuchte ich eine Lösung ohne Tabellen. Auch an der Bereich I7:X7 ist fixiert. Mit anderen Worten, erste Zahl sollte in I7 hineingeschrieben werden, und letzter Wert in X7.
Hier nochmals eine kleine Adaption zum besseren Verständnis.
https://www.herber.de/bbs/user/150332.xlsx
Besten Dank im Voraus!
Florian
Anzeige
AW: Auflistung von Werten
11.01.2022 17:08:41
Werten
Hi Florian,
hmm...dass die Textwerte absteigend in X7 beginnen sollen, hattest du im ersten Beitrag aber nicht erwähnt.
Ich versuch mich gerad an einer automatisierten VBA-Lösung - und hätte die Textwerte auch dann ab T7 eingetragen, wenn es weniger Textwerte sind, die also nicht bis X7 reichen.
Nun denn, gut, dass ich so weit noch nich bin - und wer weiß, vllt is ja wer anders mit ner brauchbaren Lösung schneller.
Ciao, bis später vielleicht
Thorsten
AW: Auflistung von Werten
11.01.2022 17:17:15
Werten
Hi Thorsten!
Ja sorry - ist immer schwer alle Randbedingungen beim ersten Mal niederzuschreiben.
Aber ja die Werte müssten von X7 absteigend hineingeschrieben werden.
Auf alle Fälle schon mal Vielen Dank fürs tüfteln.
SG Florian
Anzeige
AW: Auflistung von Werten
11.01.2022 17:17:23
Werten
Hi Thorsten!
Ja sorry - ist immer schwer alle Randbedingungen beim ersten Mal niederzuschreiben.
Aber ja die Werte müssten von X7 absteigend hineingeschrieben werden.
Auf alle Fälle schon mal Vielen Dank fürs tüfteln.
SG Florian
so, fertig :-)
11.01.2022 18:52:58
Oberschlumpf
...und funktioniert auch! - zumindest in deiner Bsp-Datei
Hi Florian,
hier, versuch mal
https://www.herber.de/bbs/user/150334.xlsm
Da ich nicht weiß, wie/wo/wann du das Makro starten willst, hab ich in deiner Tabelle einen ActiveX-Button hinzugefügt.
Ein Klick auf diesen startet 2 Durchgänge meines Makros.
Ich hab das Ganze so programmiert, dass sowohl nur Zahlenwerte als auch nur Textwerte berücksichtigt werden.
1. Klick auf den Button
2. Es wird die Spalte I, beginnend in Zeile 11, nur nach Zahlenwerten durchsucht; leere Zellen werden nicht berücksichtigt
3. Im nächsten Schritt werden alle gefundenen Zahlenwerte jeweils 1x in einer Arrayvariablen gesammelt
4. Wenn 3. abgeschlossen ist, wird hinter TABELLE1 ein neues Tabellenblatt mit dem Namen "tmp" hinzugefügt
5. In "tmp" werden zuerst die Inhalte aus der Arrayvariablen in Spalte A, beginnend in Zeile 1, eingetragen
6. Dann werden die Einträge in Spalte A aufsteigend sortiert
7. Im nächsten Schritt werden alle Einträge aus "tmp", Spalte A, in deine Zeile 7, beginnend in Spalte I (wie IDA), transporniert
8. Tabelle "tmp" wird gelöscht, da nicht mehr benötigt
9. Jetzt beginnt das Makro mit den Schritten 2. bis 8. von vorn
Unterschiede:
Jetzt werden nur die Textwerte in der Arrayvariablen gesammelt, in Tabelle "tmp" wieder aufsteigend (A-Z) sortiert, aber trotzdem so in TABELLE1, in Zeile 7 transponiert, dass der "größte" Textwert in der letzten Spalte steht.
Bitte beachte die Hinweise im Code.
Wenn du im Original auch andere Zeilen/Spalten benutzt, musst du das im Code anpassen.
Hilfts?
Ciao
Thorsten
Anzeige
AW: Auflistung von Werten
11.01.2022 18:31:51
Werten
Hi Florian
Mein Fokus liegt im Moment weniger auf VBA Krimskrams und Formeln würde ich nur mittels Hilfsspalte hinbekommen, darum lasse ich die Frage offen.
a) Für mich völlig i.O., wenn jemand PQ (mit allen Vor- und Nachteilen) für eine bestimmte Lösung nicht nutzen möchte.
b) Die PQ-Lösung macht isoliert betrachtet nicht unbedingt Sinn, aber die Aufgabenstellung genauso wenig ;)
Das Abwägen eines geeigneten Lösungswegs hängt vom Gesamtzusammenhang ab. Ich vermute da kommt noch was z.B. die Zuordnung weiterer Daten, also vielleicht eine Form der Pivotierung oder Datentransformation generell, wofür PQ bestens geeignet ist.
c) Die Aktualisierung kann man steuern/automatisieren. Einerseits mit Standardmitteln (z.B. jede Minute) oder mittels VBA. Für letzteres geht einer der Vorteile (PQ kommt ohne VBA aus) zwar verloren, andererseits erfordert es nur eine Codezeile: ThisWorkbook.RefreshAll
Die Codezeile kann man mittels Ereignis (Worksheet-Change) auslösen.
Man könnte das PQ-Ergebnis auch "versteckt" auswerfen und die Prozedur mit einer zusätzlichen Copy/Paste Routine anreichern.
Ich gebe gerne zu, dass die vorgeschlagene Kombi von PQ und VBA nicht ideal ist. Andererseits sehe ich den Lösungsweg mittels Makrorekorder in Reichweite, während eine reine VBA-Lösung (oder umfangreiche Formeln) für einen "Standardnutzer" wenig durchschaubar und somit nicht änderbar/wartbar ist.
cu
Chris
Anzeige
Bei der Version sollte man …
11.01.2022 18:48:31
RPP63
… auch die entsprechenden neuen Funktionen nutzen!
Eindeutige Zahlen transformiert aufsteigend sortiert:

=MTRANS(SORTIEREN(EINDEUTIG(FILTER(I11:I250;ISTZAHL(I11:I250)))))
dito mit Texten:

=MTRANS(SORTIEREN(EINDEUTIG(FILTER(I11:I250;NICHT(ISTZAHL(I11:I250))))))
Gruß Ralf
AW: Bei der Version sollte man …
11.01.2022 18:54:29
Oberschlumpf
...haha, wenn das wirklich soo einfach geht, dann sollte ich vllt doch langsam mal von Office 2016 zu 365 wechseln :-)
Da hätt ich mir ja ne Menge Arbeit sparen können! :-)
Ciao
Thorsten
Ja geht tatsächlich so "einfach"
11.01.2022 18:56:51
RPP63
Hier mal visualisiert:
 IJKLMN
7135145241255256 
8135145241255256 
9      
10      
11255     
12      
13      
14      
15LF05     

ZelleFormel
I8=MTRANS(SORTIEREN(EINDEUTIG(FILTER(I11:I250;ISTZAHL(I11:I250)))))

;)
Anzeige
AW: Ja geht tatsächlich so "einfach"
11.01.2022 19:11:02
Oberschlumpf
...danke!
Aber ich denk, ich bleib noch n bisschen bei 2016, 365 ist mir bei Einmalzahlung (gibts das überhaupt) wohl zu teuer, und n Abo = jeden Monat zahlen, will ich erst recht nicht.
Trotzdem:
Meine 66 Codezeilen (Leerzeilen inklu) auf nur 2 Zeilen "zusammengequetscht" ist schon beeindruckend! :-)
AW: in XL-Versionen ab 2010 ...
11.01.2022 19:54:48
neopa
Hallo Torsten,
... geht es für die Zahlenwerte aber einfacher. In I7: =WENNFEHLER(AGGREGAT(15;6;$I11:$I250/($I11:$I250&ltH7);1);"") und nach rechts ziehend kopieren.
Für die Texte wird es vor XL365 durch die fehlenden Funktionen FILTER() und EINDEUTIG() dafür viel komplizierter, aber möglich ist es auch.
In X7:
=WENNFEHLER(INDEX($I:$I;AGGREGAT(14;6;ZEILE($I$11:$I$250)/(ZÄHLENWENN($I$11:$I$250;"&lt="&$I$11:$I$250)*ISTTEXT($I$11:$I$250)=AGGREGAT(14;6;ZÄHLENWENN($I$11:$I$250;"&lt="&$I$11:$I$250)*ISTTEXT($I$11:$I$250)/(ZÄHLENWENN(Y7:$Y7;$I$11:$I$250)=0);1))/ISTTEXT($I$11:$I$250);1));"")
und Formel nach links ziehend kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: in XL-Versionen ab 2010 ...
11.01.2022 20:01:55
Oberschlumpf
Hi Werner,
auch an dich danke für die Formellösung - vielleicht kann Florian damit ja auch etwas anfangen.
Ich weiß jetzt nicht genau, ob du Derjenige bist, der um VBA einen Bogen macht :-), aber so ähnlich geht es mir mit Formeln.
Ja, so einige Formeln und daraus resultierende Konstrukte bekomm auch ich hin :-), aber ich find es anstrengend und (für mich unübersichtlich), wenn ALLE NOTWENDIGEN Parameter in einer einzigen Zeile (Formel) stehen müssen. Mit VBA kann ich mehr oder weniger jeden einzelnen Befehl in eine einzige Zeile eintragen, und nach unten für ganz ganz viele weitere VBA-Zeilen gibt es keine Grenzen - zumindest ich hab diese noch nicht erreicht. :-)
Aber trotz alledem - du gehörst hier mit zu den Formel-Gurus :-)
Ciao
tHorsten, mit H ;-)
Anzeige
AW: nun ---
11.01.2022 20:19:19
neopa
Hallo Thorsten,
... sorry für meine Nachlässigkeit beim Schreiben Deines Namen im vorigen Beitrag.
Ja, ich "mach einen Bogen um VBA" obwohl ich weiß, dass damit viel viel mehr machbar ist, als was je mit Formeln realisierbar. Aber ich brauch VBA nicht wirklich und außerdem mir persönlich gibt das Ausloten der Excelstandardfunktionalitäten und insbesondere das Erstellen von Formeln einfach mehr.
Und Florian, würde ich zumindest meine Formel für die Texte nicht empfehlen, hat er doch eine XL Version wo er Ralfs Formel nutzen kann.
Aber völlig unklar ist mir bei seiner Aufgabenstellung, ob denn wirklich immer weniger verschiedene Zahlen-/Textwerte vorhanden sind, so dass auch immer wischen I und X alle wiedergegeben werden.
Gruß Werner
.. , - ...
Anzeige
AW: eben fiel mir auf ...
12.01.2022 08:48:45
neopa
Hallo Ralf,
... dass in Deiner Formel für das Listen von Texten die Teilformel NICHT(ISTZAHL(I11:I250) sicher durch ISTTEXT(I11:I250) ersetzt werden könnte.
Da in Deinen Formel der auszuwertende Bereich nicht absolut gesetzt wurde gehe ich davon aus, dass XL365 auch nach rechts "spillt" und das deswegen nicht braucht, oder?
In dem Zusammenhang ist mir aber nun auch aufgefallen, dass Du geschrieben hast: "Eindeutige Zahlen transformiert aufsteigend sortiert: ... dito mit Texten:" und dafür Deine Formel aufgezeigt hast. Aber Florian wollte die Texte ab X7 nach links abwärts sortiert haben.
Wie muss dafür die Formellösung in XL365 aussehen? Kann XL365 nach links "spillen"?
Gruß Werner
.. , - ...
Anzeige
Nein, das geht nicht.
12.01.2022 12:34:03
lupo1
Tabellenkalkulationen orientieren sich nach rechts (Strg-R) und nach unten (Strg-U).
Übrigens kann auch Excel 2021 als erste Festversion die komplette Dynamik bis hin zu =LET() (nur kein LAMBDA). Also nicht nur 365 oder XLWeb.
Ist das ein Fest! Daher ja auch Festversion.
AW: damit also noch 365-Formel für X7 gesucht owT
12.01.2022 13:36:09
neopa
Gruß Werner
.. , - ...
Noch eine Variante
11.01.2022 20:08:31
KlausFz
Tja, da waren die Profis schneller...;-(((
...trotzdem hier noch meine Variante per VBA.
(Davon ausgehend, dass Du mit dem Bereich I7:X7 schon einen genügend großen Bereich vorgesehen hast.)
https://www.herber.de/bbs/user/150337.xlsm
Gruß!
Klaus
AW: Auflistung von Werten
12.01.2022 17:29:26
Werten
Hallo Liebe Freunde und Helfer!
ALSO DAS FORUM UND DIE LEUTE HIER SIND WIRKLICH TOP!!!!
Vielen Vielen Dank für die wirklich zeitnahe Rückmeldungen.
Habe alles ausprobiert und mit ein wenig Bastelei und ein paar Hilfszeilen und Hilfsspalten konnte ich nun die Formeln von Ralf super verwenden - wirklich genau das was ich gesucht habe!
@ Werner: Ja es haben wirklich alle unterschiedlichen Werte zwischen I und X platz. Und wie oben schon geschriben, war die Lösung von Ralf genau die richtige FÜR MICH.
Also Chris, Thorsten, Ralf, Werner, lupo1 und Klaus: VIELEN VIELEN Dank für euere rasche und professionelle Hilfe - ganz große Begeisterung wie sich hier die Leute "ehrenamtlich" ins Zeug legen.
Spitze!
Bis zum nächsten Mal und schöne Grüße!
Florian
AW: schön für Dich, doch ...
12.01.2022 19:57:41
neopa
Hallo Florian, hallo Ralf, hallo lupo
... für mich sind jetzt zumindest Fragen offen.
Florian, Du hattest in Deiner Fragestellung geschrieben: "... die Werte von X7 in Absteigender Reihenfolge geschrieben werden sollen" und in Deiner Datei die Werte von X7 nach links bis F7 auch so vorgegeben.
Ralf schrieb zu seinen Formelvorschlägen: ... aufsteigend sortiert: und " dito mit Texten... "
Lupo antwortete mir auf meine Fragestellung, ob denn die Formel nach Links "spillt": "Nein, das geht nicht. Tabellenkalkulationen orientieren sich nach rechts (Strg-R)" Dieses ist für mich voll verständlich. Aber alles andere ist für mich nicht nachvollziehbar, weil ich eben keine XL365 Version habe. Ich würde aber gern Klarheit haben, wie denn das Ergebnis nun genau aussieht bzw. wie sich die offensichtlichen Widersprüche erklären?
Gruß Werner
.. , - ...
AW: schön für Dich, doch ...
13.01.2022 06:36:56
Florian
Hallo Werner,

Florian, Du hattest in Deiner Fragestellung geschrieben: "... die Werte von X7 in Absteigender Reihenfolge geschrieben werden sollen"
und in Deiner Datei die Werte von X7 nach links bis F7 auch so vorgegeben.
Stimmt - dies war/ist für mich die Vorgabe. Hab dies mit zwei Zusatzzeilen gelöst, in welchen ich in der Ersten die Reihenfolge umgekehrt hab und dann in der Zweiten die Zahlen mit den Texten verkettet habe.
Hier das abgeänderte Beispiel das ich mal hochgeladen habe - ich hoffe das bringt Licht ins Dunkel.
https://www.herber.de/bbs/user/150362.xlsx
Und somit habe ich in der 2. neuen Zeile genau das was ich wollte: Die Zahlen von links nach rechts aufsteigend, und die Werte von Rechts nach links absteigend. Ich hoffe ich habe mich irgendwo nicht falsch ausgedrückt. Falls doch tut es mir leid - war keine Absicht - ich hab mir mühe gegeben!
Nochmals danke und schöne Grüße!
Florian
AW: mit Deiner Hilfszeilenlösung ...
13.01.2022 10:46:27
neopa
Hallo Florian,
... kommst Du natürlich zu dem von Dir angestrebten Ergebnis. Von einer solchen Hilfszeilenlösung ging ich nicht aus und solche hatte ich auch nicht für die XL365-Lösungsformeln erwartet. Danke Dir aber für Deine Klärung.
Ich meine, dass es auch in Xl365 eine hilfszeilenfreie Lösung geben müsste und werde deshalb in Deinem thread weiter danach fragen.
Gruß Werner
.. , - ...
AW: noch hilfszellenfreie XL365 Formel gesucht ...
13.01.2022 10:55:43
neopa
Hallo Formelfreaks und XL365 /XL21 -Nutzer
... ich kann mir nicht vorstellen, dass es in XL365 keine äquivalente hilfszellenfreie Formellösung zu meiner XL2010er Formel (in meinem Beitrag hier vom 11.01.2022 19:54:48) möglich sein soll. Hab die XL-Version zwar nicht, interessiert mich aber trotzdem, ob und wie es da realisiert wird.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige