Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 23:47:55
Datum ausweisen, wenn definierte Summe erreicht ist
25.04.2024 21:40:53
Excel Datenquelle aus dem Web mit Anmeldung?
25.04.2024 21:21:16
Zellkommentar in UserForm anzeigen und bearbeiten
25.04.2024 21:13:13
Kalenderwochen addieren
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

Spalteninhalte untereinander auflisten

Spalteninhalte untereinander auflisten
15.01.2022 06:53:50
erichm
Hallo,
ich habe einen Bereich mit 3 Spalten (G, H und I) und ab Zeile 2. Die Zeilen sind unterschiedlich lang belegt; dies ergibt sich aus der Spalte A. In der Zelle A1 stelle ich fest, wie viele Zeilen belegt sind.
Jetzt sollen in der Spalte L, die Spalten G, H und I untereinander aufgelistet werden.
Siehe Musterdatei, Tabelle 1 (Tabelle 2 soll nur verdeutlichen, dass mehr Zeilen belegt sein können).
https://www.herber.de/bbs/user/150396.xlsx
Alle meine Versuche mit XVERWEIS / INDEX / VERGLEICH bzw. in Kombination sind für eine Lösung gescheitert. Einige Formelmuster habe ich in der Musterdatei dargestellt.
Wie bekomme ich das hin?
Vielen Dank für eine Hilfe.
mfg

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: warum ist XL-Version nicht angegeben? ...
15.01.2022 08:19:01
neopa
Hallo Erich,
...offensichtlich hast Du XL-Version im Einsatz die jünger als XL2016 ist, denn nur da gibt es XVERWEIS() von der Du schreibst. Ich hab nur XL2016 und kann es so nicht testen. Aber möglicherweise brauchst Du Deine Formeln in G:J nur noch mit WENNFEHLER() klammern.
In meiner XL-Version würde ich es wie folgt lösen:
=WENNFEHLER(INDEX(A:A;VERGLEICH(AGGREGAT(15;6;B$2:B$99/(B$2:B$99"");ZEILE(A1));B:B;0));"")
und natürlich zuvor noch die Berechnungsoption auf automatisch stellen oder einfach [F9] betätigen.
Gruß Werner
.. , - ...
AW: warum ist XL-Version nicht angegeben? ...
15.01.2022 08:54:32
erichm
Hallo Werner,
da ist nach einer Korrektur des Beitrags leider meine Auswahl "365 privat" verschwunden; hatte ich nicht bemerkt (passiert leider immer wieder).
Die Modifizierung für die Spalten G bis I ist klar.
Ich benötige allerdings noch den Lösungsvorschlag für meine Spalte L(?).
mfg
Anzeige
AW: warum ist XL-Version nicht angegeben? ...
15.01.2022 09:44:43
neopa
Hallo Erich,
... z.B. in meiner XL-Version mit folgender Formel in L2:
=WENNFEHLER(INDEX($A:$A;VERGLEICH(AGGREGAT(15;6;INDEX(B$1:D$99;;KÜRZEN((ZEILE()-2)/17;)+1)/(INDEX(B$1:D$99;;KÜRZEN((ZEILE()-2)/17;)+1)"");REST(ZEILE()-2;A$1)+1);INDEX(B$1:D$99;;KÜRZEN((ZEILE()-2)/17;)+1);0));"")
worin ich den Formelteil: INDEX(B$1:D$99;;KÜRZEN((ZEILE()-2)/17;)+1) in meiner XL-Version als benannte Formel definiert und diese dann in der Formel anstelle dessen eingesetzt hätte. In Deiner Version kannst Du das anstelle dessen auch mit LET() tun.
Gruß Werner
.. , - ...
Anzeige
Lösung ist SUPER!
19.01.2022 11:08:25
erichm
Hallo Werner,
vielen Dank! Die Einsparung der Hilfsspalten und die benannte Formel bringen mir eine riesige Erleichterung.
Das Formelmuster kann ich für einen bestimmten Zweck mehrmals nutzen und weitere Fragestellungen beantworten. Ggfls. ergibt sich daraus nochmals eine Forumsanfrage (wird dann neuer Thread).
mfg
AW: bitteschön, doch ...
19.01.2022 11:40:38
neopa
Hallo Erich,
... in Deiner XL-Version könnten die Formeln von Boris oder lupo noch hilfreicher sein.
Gruß Werner
.. , - ...
LET-Variante...
15.01.2022 12:07:06
{Boris}
Hi,
hier der Vollständigkeit halber noch die von Werner genannte LET-Variante - bezogen auf seine Lösung:
=WENNFEHLER(LET(x;INDEX(B$1:D$99;;KÜRZEN((ZEILE()-2)/17;)+1);INDEX($A:$A;VERGLEICH(AGGREGAT(15;6; x/(x"");REST(ZEILE()-2;A$1)+1);x;0)));"")
VG, Boris
Anzeige
AW: LET-Variante...mit 2. Variable
15.01.2022 12:13:41
{Boris}
Hi,
nicht weil es besser ist, sondern um die Funktionsweise von LET etwas zu "promoten" - vielleicht hat der ein oder andere 365-User ja Lust auf solche Beispiele, um LET (besser) zu verstehen. Hier noch mit einer 2. Variablen y - am Ende ist es immer noch exakt die Formel von Werner:
=WENNFEHLER(LET(y;ZEILE()-2;x;INDEX(B$1:D$99;;KÜRZEN((y)/17;)+1);INDEX($A:$A;VERGLEICH(AGGREGAT(15;6; x/(x"");REST(y;A$1)+1);x;0)));"")
VG, Boris
oT: zu einem noch offenen anderen thread ...
15.01.2022 19:56:26
neopa
Hallo Boris,
... den thread https://www.herber.de/forum/archiv/1864to1868/1864966_Auflistung_von_Werten.html hatte ich vor 2 Tagen wieder offen gestellt. Der TE hat seine Lösung, insofern ist es jetzt nur eine Frage offen, die ich im da im letzten Beitrag am 13.01.2022 10:55:43 gestellt hatte.
Ich hab zwar noch immer kein XL365/Xl21, trotzdem interessiert mich, ob und wie in einer solchen Version eine hilfszellenfreie Lösungsformel da aussehen könnte. Wenn Du also mal Zeit und Lust dazu hast wäre nett.
Jetzt aber wünsche erst einmal einen schönes WE.
Gruß Werner
.. , - ...
Anzeige
Der Thread ist lang...
15.01.2022 20:05:40
{Boris}
Hi Werner,
...hab jetzt mal drüber gelesen - aber magst Du mir hier mal die Datei hochladen mit allen Formeln und der (Hilfszeilen-)Lösung - und dann sagen, was - vielleicht - mit xl365 "verschwinden" soll?
VG, Boris
AW: ... gekürzt wie folgt ...
16.01.2022 09:15:12
neopa
Hallo Boris,
... hier https://www.herber.de/bbs/user/150409.xlsx die Datei.
Gesucht ist das Ergebnis für die Daten in A11: A250 exakt wie in I5:X5 von Florian vorgegeben. Die Daten in Spalte A können sich natürlich ändern, es sollen aber im Ergebnis nicht mehr sein als im Bereich von I:X möglich sind. In Zeile I7 und X7 (erstellt in XL2010 auf Einsatz benannter Teilformeln hatte ich verzichtet) stehen meine Lösungsformeln, die von I7 bzw. X7 nach rechts bzw. links ziehend kopiert sind.
Die von Ralf vorgegebene XL365-Formel für die Texte: =MTRANS(SORTIEREN(EINDEUTIG(FILTER(I11:I250;NICHT(ISTZAHL(I11:I250)))))) spillt allerdings nach rechts und aufwärts sortiert. Dies hat nun Florian in einer Hilfszeile eingesetzt und die mit einer einfachen Bezugsformel in gewünschter Sortierrichtung von X7 nach links umgesetzt (bei Bedarf nach zu sehen in https://www.herber.de/bbs/user/150362.xlsx)
Meine Frage ist: wie lässt sich ohne Hilfszeile das gesuchte Ergebnis in XL365 ermitteln?
Die Sortierrichtung der Formel lässt sich sicherlich umkehren aber kann auch das spillen irgendwie abgeschaltet werden, so dass die Formel von X7 auch nach links ziehend gezogen werden kann? Bzw. wie anders sollte in XL365 die Lösungsformel aussehen.
Gruß Werner
.. , - ...
Anzeige
Ich hab noch nicht kapiert...
16.01.2022 13:23:18
{Boris}
Hi Werner,
...wieso in der Datei nur 16 Ergebniszellen vorhanden sind (I5:X5), denn I11:I250 sind ja nun mal ne Menge mehr Zellen (mit möglichen eindeutigen Treffern).
Du hast ja zudem 2 verschiedene Formeln in Zeile 7 stehen (I7:P7 und Q7:X7) - wie kommst Du auf die Bereiche - oder hast Du den vorhandenen Bereich einfach durch 2 geteilt?
Ich hab halt noch nicht kapiert, warum dei eine Auflistung von links und die andere vor rechts beginnen soll - und ob der Ergebnisbereich (I5:X5) überhaupt ausreichend ist.
Wenn man nämlich nur I11:I250 auf "" prüft, dann spillt die Formel ja das komplett richtige Ergebnis:
Userbild
Vielleicht kannst Du mir meine Fragen ja irgendwie beantworten.
VG, Boris
Anzeige
AW: ... hatte ich auch nicht, war/ist Vorgabe ...
16.01.2022 14:01:32
neopa
Hallo Boris,
... Florian wollte das genau so. Das hatte er auf meine Nachfrage so bestätigt und auch dass es keine Überschneidungen geben könne und die Texte abwärts sortiert beginnend von X7 nach links ohne Duplikate gelistet werden soll.
Ralf hatte auch zwei XL653-Formeln eingestellt. Für die Formel in I7: =MTRANS(SORTIEREN(EINDEUTIG(FILTER(I11:I250;ISTZAHL(I11:I250)))))
Aber meine Frage berührt nur die Formel ab X7 nach links.
Gruß Werner
.. , - ...
Ok...
16.01.2022 14:25:10
{Boris}
Hi Werner,
um das Spillen zu verhindern, muss man das MTRANS(EINDEUTIG(FILTER(....)))-Array mit INDEX umschließen, um dann daraus einzelne die Einträge auszulesen.
Das kann man dann noch mit LET verkürzen, so dass
X7:
=LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$11:$I$250;$I$11:$I$250""))));INDEX(x;ANZAHL2(x) -ANZAHL2(Y7:$Y7)))
dann nach links kopiert werden kann.
Ist es das, was Du meintest?
VG, Boris
VG, Boris
Anzeige
AW: danke, gut zu wissen, ...
16.01.2022 14:47:56
neopa
Hallo Boris,
... dass man das Spillen verhindern kann und das dies zumindest mit INDEX() -Funktion (ist eh meine Lieblingsfunktion) möglich ist. Ich kann es leider nicht nachstellen, aber ich kann es mir hoffentlich merken, wenn ich später mal dies brauchen sollte.
Ich wundere mich nur, dass man seitens der XL-Progranmmierer keine entsprechende Deaktivierungsfunktionalität in Form einer Funktion oder zumindest eines Funktionsarguments zur Richtungswahl bzw. Stoppen des Spillens vorgesehen hat..Aber es ist sicherlich auch ganz selten überhaupt notwendig.
Nochmals Danke für Deine zusätzlichen Bemühungen für meinen Spezialwunsch..
Schönen Sonntag Dir noch.
Gruß Werner
.. , - ...
Anzeige
Man kann das Spillen...
16.01.2022 18:49:25
{Boris}
Hi Werner,
...übrigens auch anders unterdrücken - und zwar mit {Strg+Shift+Enter} ;-)
=INDEX(A1:A3;)
als Arrayformel eingegeben, liefert nur den Wert aus A1. Ohne die { } werden die 3 Werte aus A1:A3 gespillt.
VG, Boris
AW: noch besser zu wissen; danke :-) owT
16.01.2022 18:54:42
neopa
Gruß Werner
.. , - ...
@{Boris}
17.01.2022 13:53:11
Charly
Hi Boris
Ich hab Gestern den ganzen Beitrag mitgelesen.
Dabei ist mir was aufgefallen.
Ich hab in der Datei was dazu geschrieben.
https://www.herber.de/bbs/user/150440.xlsx
Wäre schön wenn du die Zeit findest mal reinzuschauen.
Danke vorab.
Gruß Charly
Anzeige
AW: @{Boris}
17.01.2022 14:11:48
{Boris}
Hi Charly,
in diesem Fall kann man z.B. das INDEX-Ergebnis auf ISTZAHL prüfen - falls WAHR, dann Zelle leer.
X6:
=LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$8:$I$250;$I$8:$I$250""))));y;INDEX(x;ANZAHL2(x) -ANZAHL2(Y6:$Y6));WENN(ISTZAHL(y);"";y))
und nach links bis Q6 kopieren.
VG, Boris
Danke {Boris}
17.01.2022 14:17:36
Charly
Vielen Dank für die schnelle Antwort Boris.
Perfekt
Mal abwarten was der Fragesteller dazu sagt.
Gruß Charly
AW: Doch noch nicht ganz ...
17.01.2022 15:25:10
{Boris}
Hi,
der Überlauf entsteht, wenn der Zähler für INDEX negativ wird. Demnach
X6:
=LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$8:$I$250;$I$8:$I$250""))));y;INDEX(x;MAX(1;ANZAHL2(x) -ANZAHL2(Y6:$Y6)));WENN(ISTZAHL(y);"";y))
und nach links kopieren.
VG, Boris
Das ist die Lösung
17.01.2022 15:33:26
Charly
Hi Boris
Ich dachte schon man kann in diesem Fall die schönen neuen Funktionen nicht verwenden.
Vielen Dank
Gruß Charly
AW: ich hätte etwas anderes vermutet ...
17.01.2022 14:27:01
neopa
Hallo Charly,
... Boris hat Dir inzwischen schon geantwortet
Deine Datei in meiner XL-Version zeigt mir, dass in S6:Q6 noch die größten Zahlenwerte ermittelt werden. Ich vermutete, dass der FILTER()-Formelteil wie folgt erweitert werden sollte. ... FILTER($I$8:$I$250;$I$8:$I$250"";ISTTEXT($I$8:$I$250))... und evtl. die gesamte Formel noch mit WENNFEHLER() geklammert werden muss. Teste mal., bitte auch mal ohne den INDEX()-Formel-Teil dafür mit dem Formeleingabeabschluss: Strg+Shift+Enter wie Boris als Alternative angegeben hatte.
Gruß Werner
.. , - ...
AW: ich hätte etwas anderes vermutet ...
17.01.2022 15:02:11
Charly
Hi Werner
Könntest du bitte die kompletten Formeln zum testen aufschreiben.
Danke
sh. auch Beitrag an Boris.
Gruß Charly
AW: hierzu ...
17.01.2022 15:30:20
neopa
Hallo Charly,
... kann ich nur das Ergebnis in der an Boris eingestellten Datei beurteilen und das ist mE korrekt.
Die komplette Formel wie ich vermutet hatte, dass es auch zum Ergebnis führen könnte, wäre auf der von Boris im Beitrag 17.01.2022 14:11:48 gezeigten Formel
in X6: =WENNFEHLER(LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$8:$I$250;$I$8:$I$250"";ISTTEXT($I$8:$I$250)))));y;INDEX(x;ANZAHL2(x) -ANZAHL2(Y6:$Y6));WENN(ISTZAHL(y);"";y));"")
Gruß Werner
.. , - ...
AW: hierzu ...
17.01.2022 15:53:17
Charly
Hi Werner
Boris seine zuletzt gepostete Formel liefert das gewünschte Ergebnis.
=LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$8:$I$250;$I$8:$I$250""))));y;INDEX(x;MAX(1;ANZAHL2(x) -ANZAHL2(Y6:$Y6)));WENN(ISTZAHL(y);"";y))
Wenn ich deinen Vorschlag einbaue ist das Ergebnis auch richtig.
=LET(x;MTRANS(SORTIEREN(EINDEUTIG(FILTER($I$8:$I$250;$I$8:$I$250"";ISTTEXT($I$8:$I$250)))));y; INDEX(x;MAX(1;ANZAHL2(x) -ANZAHL2(Y6:$Y6)));WENN(ISTZAHL(y);"";y))
Ist aber hier nicht notwendig.
Ich muss erst mal Boris seine letzte Formel aufdröseln.
Ich bin ja nicht der Formelprofi so wie ihr.
Danke Werner
Gruß Charly
Danke für den Zusatzservice Boris; owT
19.01.2022 11:42:43
erichm
...
Mit LET und 51 und 17 als Argument gespillt
17.01.2022 13:02:32
lupo1
=LET(n;51;o;17;
SORTIERENNACH("Muster"&REST(SEQUENZ(n)-1;o)+1;
ZUFALLSMATRIX(n)+KÜRZEN(SEQUENZ(n;;0;1/o))))
mit der RPP63-Formel
AW: Mit LET und 51 und 17 als Argument gespillt
19.01.2022 12:48:15
erichm
Danke; habe ich erst jetzt entdeckt; melde mich noch.
mfg
Auch eine Super Lösung!
19.01.2022 16:27:13
erichm
Danke lupo1!
Diese Variante ist genial....
.... muss ich mir noch näher durch den Kopf gehen lassen.
Ich habe jetzt alle 3 Lösungen in eine Musterdatei eingestellt:
https://www.herber.de/bbs/user/150515.xlsx
Vielen Dank an alle!!
mfg
Noch eine Nachfrage....
19.01.2022 19:00:52
erichm
Hallo,
wenn ich statt "Muster1 bis 17" ganz unterschiedliche Zellinhalte wähle; kann man das dann ebenfalls ohne weitere Hilfsspalten erledigen?
Meine Versuche scheitern da natürlich.
Musterdatei mit den geänderten Zellinhalten:
https://www.herber.de/bbs/user/150519.xlsx
Vielen Dank nochmal.
mfg
INDEX vorziehen, "Muster"& wegnehmen
21.01.2022 12:42:42
lupo1
=LET(x;B1:B17;o;ANZAHL2(x);n;o*3;
INDEX(x;SORTIERENNACH(REST(SEQUENZ(n)-1;o)+1;
ZUFALLSMATRIX(n)+KÜRZEN(SEQUENZ(n;;0;1/o)))))
Phantastisch - klappt perfekt! DANKE! mfg, owT
21.01.2022 18:38:24
erichm
...

303 Forumthreads zu ähnlichen Themen

Frage - Spalten einfügen & Spalten untereinander z
André

Hallo zusammen!
Ich habe da als blutiger Anfänger in VBA zwei Fragen:
1.) Wie kann ich in meiner Tabelle in jeder zweiten Spalte 4 leere Spalten einfügen lassen?
2.) Dann möchte ich unter den Inhalt der ersten fünf Spalten (A-E) den Inhalt der zweiten 5 Spalten (F-J) usw. In der grö...
08.08.2008 16:00:00
Thread-ID: 1000340
3 Beiträge
Anzeige
Spalten in Zeilen anhängen
Jörg

Hallo zusammen,
in einer Tabelle mit ~1000 Artikeln in Spalte A stehen jeweils in den Spalten B-M die 12 zum Artikel gehörenden Monatswerte des Verbrauchs.
Ich brauche diese Daten jetzt so angeordnet, dass weiterhin alle Artikel in Spalte A stehen, jedoch die einzelnen Monatswerte unter...
01.09.2008 18:03:42
Thread-ID: 1006570
11 Beiträge
Zelleninhalt (Text) in 2 Spalten teilen
El.

Hallo!
Habe eine Spalte in der Artikelname und Artikelmerkmale, durch drei Punkte getrennt "...", zusammen stehen. Beispiel: Lecker Whisky 21 Jahre ... Lieferung in hochwertiger Schatulle
Wie krieg ich nun den Text links der Punkte in eine extra Spalte und rechts der Punkte in eine extr...
03.09.2008 16:35:00
Thread-ID: 1007155
4 Beiträge
Anzeige
Spaltentrennzeichen nach speichern in Text-Datei
Michael

Hallo
ich habe ein Problem. Vielleicht ist es schwierig, vielleicht aber auch nicht.
Ich habe eine Text-Datei mit Spaltentrennzeichen "|" (besser als ";" der Übersichtlichtlichkeit halber) und verschiedenen Längen der Spalten.
Ich will diese in Excel überarbeiten und als Text-Dat...
11.09.2008 09:28:00
Thread-ID: 1008978
3 Beiträge
Text in Spalten
Simone

Hallo zusammen,
ich hab ein wohl eher triviales Problem, komme aber trotzdem einfach nicht weiter.
Ich füge in ein Arbeitsblatt Daten aus einem anderen Programm ein. Jetzt habe ich mehrmals die Funktion 'Text in Spalten' verwendet / ausprobiert, und wenn ich jetzt wieder Daten in A1 ein...
16.09.2008 13:54:00
Thread-ID: 1010092
3 Beiträge
Zeilen löschen in Abhängigkeit von 2 Spalten
2

Hallo zusammen,
ich habe ein Makro über den Rekorder aufgezeichnet und versuche es anzupassen. Leider kenn ich mich in der Syntax nicht aus.
Ziel:
In einem markierten Bereich die Zeilen löschen, die in Abhängigkeit von zwei Spalten eine 0 enthalten.
D.h. Spalten X und Y enthalte...
06.10.2008 14:00:00
Thread-ID: 1014434
3 Beiträge
TextBox Werte in Spalte Q ersetzen ?
Selma

Hallo Leute,
ich habe eine Frage zu UserForm. Beim Klick auf CommandButton1 sollen die Zahlen aus TextBox1 mit Werten in Spalte Q ab Zeile 12 ersetzt werden. Dies soll nur dann erfolgen, wenn in TextBox1 die Zahlen stehen bzw. der TextBox gefüllt ist.
Ersetzt sollen nur die vorhandene W...
13.10.2008 15:42:00
Thread-ID: 1016083
3 Beiträge
Text in Spalten
Tom

Hallo,
ich möchte mit der Funktion Text in Spalten erreichen, dass alle Zeilen, die mit einer Zahl beginnen, nach der Zahl in eine neue Spalte geschoben werden - nur finde ich hierfür nur die Funktionen nach Leerzeichen, Komma usw...
Wer hat ne Lösung?
In A1: 1020 Maier Huber Mülle...
20.10.2008 14:09:42
Thread-ID: 1017654
7 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
2
3
10
3
3
2
2
2
2
6
3
2
2
2
2
4
4
8
4
5
4
2
5
3
4