Liste generieren mit einzelnen Zeilen pro befülltem Feld

[thrxl]

Hallo in die Runde!

Nachdem ich jahrelang immer wieder stiller Mitleser war und hier schon oft auf die Lösungen zu meinen Fragen gestoßen bin, habe ich mich für mein heutiges Problem angemeldet und hoffe, dass mir hier geholfen werden kann.

Ich muss eine Datei erstellen, über die ich eine Liste zum Upload in Salesforce erstellen kann. Ich habe eine stark vereinfachte Beispieldatei in den Anhang gepackt, über die ich es veranschaulichen mag. Tatsächlich werden diese Listen viel umfangreicher und jeweils auf einzelnen Blättern sein.

Wir haben die Hauptliste, in die ich die Veranstaltungsorte für Messen eintrage, sowie die Kunden, die dort buchen, und was sie buchen. Dabei haben wir immer ein Paket in Verbindung mit optionalen weiteren Produkten. Die Preise hinterlege ich in seperaten Hilfslisten.

Für die Zieldatei benötige ich eine Zeile pro gebuchtem Paket und Produkt. Bisher hatten wir für einzelne Posten einzelne Zeilen in der Quelldatei, was ich dann halbwegs elegant über Sverweise und Filter lösen konnte, nun sieht es allerdings anders aus. Da hier aus einer Zeile in der Hauptliste zwischen einer und (in der Beispieldatei) vier Zeilen in der Zielliste generiert werden müssen, bin ich etwas ratlos, welche Möglichkeit bzw welches Tool ich hier verwenden kann, meine Excel-Kenntnisse reichen nicht sehr weit über die Basisformeln hinaus.

Ich bin natürlich gewillt, mich in ein Thema einzuarbeiten, aber brauche einen Denkanstoß bzw einen Hinweis, in welche Richtung ich gehen muss, um dieses Problem zu lösen.

Vielen Dank schonmal und viele Grüße aus Mittelfranken,
Thorsten

[Elex]

Hallo

Denkanstoß. Der Anfang wäre "entpivotieren".
z.B: mit power query entpivotieren

Gruß Elex

[thrxl]

Super, danke, das sieht schon sehr hilfreich aus! Ich lese mich gleich ein - Dankeschön!

[Ralf A]

...entpivotieren wird da nicht reichen. Die Struktur muss geändert werden. Es ist kein Zusammenhang zwischen Produkten und Paketen  pro Datensatz (gleiche Zeile) zu erkennen. Die Produkte werden in der Hauptliste horizontal und die Pakete vertikal aufgeführt, in der Hilfsliste also vertikal und im Ziel (Zeile 16), wird als Preis 100 ausgegeben, obwohl Paket L 800 kostet.
Ich sehe zwar, dass weiter unten Paket L nochmal extra aufgeführt ist, dennoch ist der Aufbau inkorrekt und so nicht auszuwerten (oder bestenfalls mit etlichen unnötigen Klimmzügen)
Also entweder es muss eine Beziehung zwischen Paketen und Produkten aufgebaut werden oder die Pakete müssen ebenfalls in jeweils separaten Spalten in der Hauptliste aufgeführt werden oder es bedarf einer 2. Hilfsliste (eine für Produkte, eine für Pakete)oduktM
1

Code:

Momentan sähe eine entpivotisierte Tabelle so aus:
Veranstaltungsort    Firma    Paket    Produkt    Anzahl
Köln    A GmbH    S    Produkt B    1
Köln    A GmbH    S    Produkt C    2
Köln    D GmbH    L    Produkt A    1
Köln    D GmbH    L    Produkt B    3
Köln    D GmbH    L    Produkt C    2
Köln    E GmbH    S    Produkt B    2
Mainz    C GmbH    M    Produkt B    2
Mainz    F GmbH    L    Produkt A    2
Mainz    F GmbH    L    Produkt C    1
Stuttgart    B GmbH    L    Produkt A    2
Stuttgart    B GmbH    L    Produkt C    1

(Die letzten beiden Zeilen sind in der Darstellung hier verschoben, weil Stuttgart zu lang ist.... :) )

Veranstaltungsort

[thrxl]

...oder die Pakete müssen ebenfalls in jeweils separaten Spalten in der Hauptliste aufgeführt werden...

Danke für den Hinweis! Das wäre tatsächlich kein Problem, es sind insgesamt 40 Produkte, da kommt es nicht darauf an, ob ich die Pakete in 3 seperaten Spalten mit aufführe. Da muss ich dann lediglich schauen, dass dort nur jeweils ein Feld befüllt sein darf, das ist aber eine interne Sache und kein größeres Problem. :)

[Ralf A]

...wenn das kein Problem ist, dann könntest Du so vorgehen...

[maninweb]

Hallo,

das geht auch per Formel, sofern Du ein entsprechend modernes Excel hast ...

PHP-Code:

=LET(Kunden; $A$4:$B$9; 
     Produktnamen; $C$3:$F$3; 
     Produktdaten; $C$4:$F$9; 
     Preise; $H$4:$I$9; 
     vnRows; ZEILEN(Kunden) * SPALTEN(Produktdaten); 
     vnCustomers; MATRIXERSTELLEN(vnRows; SPALTEN(Kunden); LAMBDA(X;Y; INDEX(Kunden; AUFRUNDEN(X / SPALTEN(Produktdaten); 0); Y))); 
     vnProducts; MATRIXERSTELLEN(vnRows; 2; LAMBDA(X;Y; 
       LET(N; AUFRUNDEN(X / SPALTEN(Produktdaten); 0); 
           P; WENN(REST(X; SPALTEN(Produktdaten)) > 0; REST(X; SPALTEN(Produktdaten)); SPALTEN(Produktdaten)); 
              WAHL(Y; INDEX(Produktnamen; 1; P) & WENN(P < 2; " " & INDEX(Produktdaten; N; 1); ""); WENN(P > 1; INDEX(Produktdaten; N; P); 1))))); 
     vnPrices; MATRIXERSTELLEN(ZEILEN(vnProducts); 1; LAMBDA(X;Y; 
       LET(N; WENNFEHLER(VERGLEICH(INDEX(vnProducts; X; Y); INDEX(Preise; 0; 1); 0); 0); 
              WENN(N > 0; INDEX(Preise; N; 2); 0)))); 
     vnMatrix; HSTAPELN(vnCustomers; vnProducts; vnPrices); 
     FILTER(vnMatrix; INDEX(vnMatrix; 0; SPALTEN(vnMatrix) - 1) > 0; 0)) 


Gruß

[{Boris}]

Hi Mourad,

Respekt! Immer wieder schön zu sehen, wie mächtig die neuen Funktionen sind

[maninweb]

Hallo Boris,

Danke. Stimmt, diese neuen Funktionen haben es in sich. :-)

Gruß

[{Boris}]

Hi Mourad,

als absolutes i-Tüpfelchen kann man noch die Spaltenüberschriften direkt mit stapeln:

=LET(Kunden; A4:B9;
    Produktnamen; C3:F3;
    Produktdaten; C4:F9;
    Preise; H4:I9;
    vnRows; ZEILEN(Kunden) * SPALTEN(Produktdaten);
    vnCustomers; MATRIXERSTELLEN(vnRows; SPALTEN(Kunden); LAMBDA(X;Y; INDEX(Kunden; AUFRUNDEN(X / SPALTEN(Produktdaten); 0); Y)));
    vnProducts; MATRIXERSTELLEN(vnRows; 2; LAMBDA(X;Y;
      LET(N; AUFRUNDEN(X / SPALTEN(Produktdaten); 0);
          P; WENN(REST(X; SPALTEN(Produktdaten)) > 0; REST(X; SPALTEN(Produktdaten)); SPALTEN(Produktdaten));
              WAHL(Y; INDEX(Produktnamen; 1; P) & WENN(P < 2; " " & INDEX(Produktdaten; N; 1); ""); WENN(P > 1; INDEX(Produktdaten; N; P); 1)))));
    vnPrices; MATRIXERSTELLEN(ZEILEN(vnProducts); 1; LAMBDA(X;Y;
      LET(N; WENNFEHLER(VERGLEICH(INDEX(vnProducts; X; Y); INDEX(Preise; 0; 1); 0); 0);
              WENN(N > 0; INDEX(Preise; N; 2); 0))));
    vnMatrix; VSTAPELN({"Ort"."Firma"."Produkt"."Menge"."Preis"};HSTAPELN(vnCustomers; vnProducts; vnPrices));
    FILTER(vnMatrix; INDEX(vnMatrix; 0; SPALTEN(vnMatrix) - 1) > 0; 0))

Aber das ist wirklich nur eine Randnotiz.