Office: Alle Kombinationen mehrerer Einträge pro Spalte listen

Einleitung

<pre><b>__|_____A_____|_______B_______|__C__|CODETab von lupo1 1|Wasserpumpe|Ovalflansch |10ccm| 2|Ölpumpe |Rundflansch |20ccm| 3| |Vertikalflansch| |</b></pre>soll - alles mit jedem kombiniert - werden zu:

<pre><b>__|_____A_____|_______B_______|__C__|CODETab von lupo1 8|Wasserpumpe|Ovalflansch |10ccm| 9|Wasserpumpe|Ovalflansch |20ccm|10|Wasserpumpe|Rundflansch |10ccm|11|Wasserpumpe|Rundflansch |20ccm|12|Wasserpumpe|Vertikalflansch|10ccm|13|Wasserpumpe|Vertikalflansch|20ccm|14|Ölpumpe |Ovalflansch |10ccm|15|Ölpumpe |Ovalflansch |20ccm|16|Ölpumpe |Rundflansch |10ccm|17|Ölpumpe |Rundflansch |20ccm|18|Ölpumpe |Vertikalflansch|10ccm|19|Ölpumpe |Vertikalflansch|20ccm|</b></pre>
Lösung

Mit

A6[:D6]: =MAX(1;PRODUKT(ANZAHL2(A1:A5);B6))

ermitteln wir die Anzahlen, ab welcher sich die jeweilige Spalte wiederholt. Die letzte Spalte (C) dreht sich dabei am häufigsten, ist also nur 2 Einträge lang. Da die Spalte B der Spalte C gegenüber 3 "übergeordnete" Einträge hat (das ist jetzt einfach so behauptet), benötigt sie 2x3=6 Einträge, bis sie wieder von vorn beginnt. Und Spalte A ist die im Beispiel gewählte "Tophierarchie" mit 2x3x2=12 Einträgen. Das Ganze gelingt mit Hilfe von PRODUKT. Eine künstliche Spalte D mit dem künstlichen Wert 1 benötigen wir hierfür jedoch noch als Anker:

<pre><b>__|_____A_____|_______B_______|__C__|__D_|CODETab von lupo1 6| 12,00| 6,00| 2,00|1,00|</b></pre>A8[:C19]: =INDEX(A$1:A$5;REST((ZEILE(A1)-1)/B$6;A$6/B$6)+1)

sorgt nun für die 12zeilige Verwertung der Kopfzeilen 1:5 (die im Beispiel aber nur bis Zeile 2 oder 3 gefüllt sind). In der Einleitung steht schon das gewünschte Ergebnis, welches hier nicht wiederholt wird.


Baue Dir die Datei selbst

Führe dafür einfach folgendes Makro aus:

Sub EintraegeVonSpaltenKombinieren()
Workbooks.Add xlWorksheet
ActiveWorkbook.Names.Add Name:="KombinationenAbHier", _
RefersToR1C1:="=MAX(1,PRODUCT(COUNTA(R[-5]C:R[-1]C),RC[1]))"
ActiveWorkbook.Names.Add Name:="Kombinationsfeld", _
RefersToR1C1:="=INDEX(R1C:R5C,MOD((ROW(R[-7]C)-1)/R6C[1],R6C/R6C[1])+1)"
[A1:C1] = Split("Wasserpumpe Ovalflansch 10ccm")
[A2:C2] = Split("Ölpumpe Rundflansch 20ccm")
[A3:C3] = Split(" Vertikalflansch ")
[A6:D6] = "=KombinationenAbHier"
[A8:C19] = "=Kombinationsfeld"
[A1:D5].Interior.Color = 44444
[A6:D6].Interior.Color = 22222
[A8:C19].Interior.Color = 55555
End Sub

Anmerkungen:

• Die definierten Namen im Erstellungsmakro bewirken, dass in der erstellten Tabelle Spalten und Zeilen beliebig eingefügt, gelöscht und verschoben werden dürfen, was mit normalen Formeln nicht geht. Nur die letzte Spalte (hier: D) muss als jeweils letzte Spalte (also auch nach Einfügen oder Löschen) erhalten bleiben, da der Wert 1 in Zeile (hier) 8 benötigt wird.
  
• Die Zuweisung definierter Namen als Zellformeln funktioniert in VBA auch ohne .Formula bzw. .FormulaR1C1