Excel Verweis mit Versatz und Lücken

[MaLin]

Hallo zusammen,

nachdem ich neulich eine Frage hatte zum Verschieben von Verweisen und dem Berücksichtigen von Versätzen, stoße ich nun erneut an meine Grenzen.

Habe folgendes Problem:
Für einen Code in der IT-Abteilung muss ich für ein einfaches copy-paste ein bestimmtes Schema einhalten.

D. h. aus einer Datentabelle will ich in einer Spalte auf entsprechende Codewerte verweisen.
Diese sind immer einem Artikel zugeordnet und sollen in einer langen Liste untereinander stehen.

Problem:
Die Datentabelle ist vertikal gegliedert nach Artikelname und den darunter liegenden Codes. --> soweit gut

Die Spalte die ich für den Kollegen benötige muss aber immer wiederkehrend eine Überschrift enthalten, darunter den Artikelnamen, darunter die Codewerte (immer die gleiche Anzahl an Spalten), danach ein Endsymbol und dann geht es genauso weiter.

Durch diese formativen Überschriften usw. habe ich natürlich Sprünge in den Verweisen auf die Codewerte.

Wie kann man das per Formel oder dergleichen anpassen, da wir hier in Richtung von 30.000 Zeilen gehen?

Hier ein Beispiel:
In Spalte "D" soll ich dieser Form immer auf die Werte aus Spalte "A2 verwiesen werden; aber immer mit den Fett und Kursiv eingefügten Unterbrechungen



Ich hoffe es ist ersichtlich dass sich der Code-Bereich immer etwas nach unten verschiebt.
Wie kann ich da eine Formel einstellen ohne Werte auszulassen, die durch die Überschriften und Endsymbole entstehen?

Dankeschön

[HKindler]

Hi,

Für ein aktuelles Excel:

Code:

=LET(
a;B3:B99999;
b;15;
c;ÜBERNEHMEN(a;ANZAHL2(a));
d;SPALTENUMBRUCH(c;b;"");
e;MATRIXERSTELLEN(2;SPALTEN(d);LAMBDA(z;s;WAHL(z;"Ende";"Überschift")));
f;VSTAPELN(d;e);
g;ZUSPALTE(f;;1);
h;WEGLASSEN(g;-1);
h)

a legt den Bereich fest
b Anzahl Zeilen von "Artikel" bis "code14"
c beschränkt den angegebenen Bereich auf gefüllte Zellen
d bricht die Spalte nach b Zeilen um, so dass man die Blöcke nebeneinder stehen hat
e erzeugt eine Matrik mit zwei Zeilen und der Anzahl Spalten von d mit den Texten "Ende" und "Überschrift"
f erzeugt eine Matrix mit d und e übereinander
g schnappt sich die einzelnen Spalten von f und stapelt diese übereinander
h schneidet das letzte "Überscrift" weg

Und das nächste mal bitte kein Bild, sondern eine Datei zur Verfügung stellen!

[MaLin]

klasse,

das funktioniert wunderbar.

Vielen Lieben dank.

wie kann man das noch erweitern wenn man immer an dritter stelle nach "Artikel" eine bestimmte Bezeichnung noch einfügen will und sich damit alles weitere nach unten verschiebt?

Ich hätte gerne immer direkt nach Artikel das Wort: Segment

und alles Weitere soll dann wie oben beschrieben so folgen

kann man das in die formel einbauen?

[HKindler]

Hi,

ist "Artikel" ein fester Text, wie in deinem Bild suggeriert, oder wechselt der Text?
Wo ist die Beispieldatei mit Wunschergebnis?
Muss man wieder eine Woche auf eine Rückmeldung warten?

[MaLin]

Hi,

sorry war eine Woche im Krankenstand.

Hier mal eine Beispieldatei.

In Spalte "F" spielt sich die Formel ab.
Im Grunde ist die soweit wunderbar.

Hatte nur vergessen dass ich immer nach der variierenden Artikelbezeichnung (<--Artikel)
immer den Zusatz "Segment" darunter benötige.

[HKindler]

Hi,

Code:

=LET(a;D4:D100000;
b;25;
c;ÜBERNEHMEN(a;ANZAHL2(a));
d;SPALTENUMBRUCH(c;b;"");
d_1;ÜBERNEHMEN(d;1);
d_2;WEGLASSEN(d;1);
e;MATRIXERSTELLEN(2;SPALTEN(d);LAMBDA(z;s;WAHL(z;"],),";"OptionCode(modelCode:")));
e_1;MATRIXERSTELLEN(1;SPALTEN(d);LAMBDA(z;s;"Segment"));
f;VSTAPELN(d_1;e_1;d_2;e);
g;ZUSPALTE(f;;1);
h;WEGLASSEN(g;-1);
h)

Die neu hinzugekommenen bzw. ergänzten Variablen solltest du mittlerweile verstehen. Wenn nicht, einfach nochmals nachfragen.

[MaLin]

wow, ja funktioniert einwandfrei

danke schön

[MaLin]

welche Sphären von Excel hast du hier denn beschritten?

Ich muss gestehen, dass ich in diesen Bereich noch nie vorgedrungen bin.

[HKindler]

Ein recht spannendes Thema, diese neuen Spill-Funktionen. Und für mich auch noch relativ neu und spannend. Aber es lohnt sich, sich mit denen auseinander zu setzen.