Transponieren mit VBA

Hallo zusammen,
ich hab ein etwas komplexeres Excel-Problem, bei dem ich mir hier Hilfe erhoffe.
Ich habe eine große Anzahl einzelner Excel-Dateien.
Darin sind eine oder mehrere Tabellenblätter.
Diese sind folgendermaßen aufgebaut:
Spalten: Nr. / Wert1 / Wert2 / Wert3 / Datum
Zeilen:
1 / 1,5 / 2,3 / 3,1 / 01.01.2016
2 / 2,5 / 1,3 / 2,2 / 02.01.2016
3 / 0,5 / 0,3 / 18 / 03.01.2016
oder manche haben auch:
Spalten: Nr. / Wert1 / Wert2 / Wert3 / Wert4 / Wert5 / Datum
(und die Zeilen dementsprechend mit 5 Werten befüllt)
Darunter sind mehrere Zeilen mit entsprechenden Werten ausgefüllt. Möglich sind wohl 1 Zeile bis max. 50 Zeilen.
Jetzt müsste mir das VBA die Excel so umformen, dass ich eine Excel-Datei bzw. ein Tabellenblatt heraus bekomme, welches mir pro Spalte "Wert" die Daten in diese Form bringt:
Spalten: Nr.NEU / Wert / Datum
Zeilen:
1+[Spaltenname von Spalte Wert1] / Wert1 / Datum Nr.1
1+[Spaltenname von Spalte Wert2] / Wert2 / Datum Nr.1
1+[Spaltenname von Spalte Wert3] / Wert3 / Datum Nr.1
2+[Spaltenname von Spalte Wert1] / Wert1 / Datum Nr.2
2+[Spaltenname von Spalte Wert2] / Wert2 / Datum Nr.2
2+[Spaltenname von Spalte Wert3] / Wert3 / Datum Nr.2
3+[Spaltenname von Spalte Wert1] / Wert1 / Datum Nr.3
usw.
Ich hoffe, mich verständlich ausgedrückt zu haben.
Grüße
Dawson

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

…Dawson,
die allerdings Hilfszellen benötigt und 2 meiner Standard-UDFs benutzt, die in der benötigten Version auch im Archiv enthalten sind. In der hier verlinkten BspDatei siehst du auch, wie sie in deiner Mappe untergebracht wdn müssen. Der nachfolgende blaue Text ist für die vorgelegte Lösung irrelevant und wendet sich an Nutzer, die bereits UDFs von mir benutzen bzw zukünftig verwenden wollen.
Die vorangestellte und von VJoin benutzte Enumeration cxTriState wurde in bisher unveröffentlichten Versionen von VJoin, VSplit (aktuell zZ beide in Vs1.5) u.a. (bereits veröffentlichten) UDFs geändert:
alt: Public Enum cxTriState: cxAsUsed = -2: cxRTrue: cxFalse: cxPTrue: End Enum
neu: Public Enum cxTriState: cxAsUsed = -2: cxTrue: cxFalse: cxCTrue: End Enum
Die Enum und die entsprd SchlüsselWorte in der UDF perspektivisch ggf ändern, damit es keine Konflikte mit anderen Versionen gibt, falls diese mal in einem AddIn oder der Personal.xlsb zusammengefasst wdn sollen.
Mein Lösungsvorschlag sähe dann so aus:

	A	B	C	D	E	F	G	I	J	K
1	Nr	Wert 1	Wert 2	Wert 3	Datum \| Wert 4	leer \| Wert 5	leer \| Datum
2		2	3	4	5	6		*Hilfsspalte1*	*Hilfsspalte2*	*Hilfsspalte3*
3	123	3,5	2,5	1,6	12.02.2016			123-2 123-3 123-4	3,5 2,5 1,6	42412 42412 42412
4	333	9	7	4,9	04.05.2016			333-2 333-3 333-4	9 7 4,9	42494 42494 42494
5	567	5	6	4	07.08.2016			567-2 567-3 567-4	5 6 4	42589 42589 42589
6	444	2,2	2,3	1,4	3,5	2,6	12.08.2016	444-2 444-3 444-4 444-5 444-6	2,2 2,3 1,4 3,5 2,6	42594 42594 42594 42594 42594
7
8			Nr. (neu)	Wert	Datum			*PrimärFormel-Bsp f.QuellZeile3:*
9	B	1	123-2	3,5	12.02.2016			123-2	3,5	12.02.2016
10	C	1	123-3	2,5	12.02.2016			123-3	2,5	12.02.2016
11	D	1	123-4	1,6	12.02.2016			123-4	1,6	12.02.2016
12	B	2	333-2	9	04.05.2016		*Formeln:*
13	C	2	333-3	7	04.05.2016		I3[:K6]: {=VJoin(VSplit(GLÄTTEN(VJoin(WENNFEHLER(INDEX($A3:$G3;1;WAHL(SPALTE(A1);1;2;ANZAHL
14	D	2	333-4	4,9	04.05.2016		($A3:$G3))):INDEX($A3:$G3;1;WAHL(SPALTE(A1);1;ANZAHL($A3:$G3)-1;ANZAHL($A3:$G3)))&WAHL
15	B	3	567-2	5	07.08.2016		(SPALTE(A1);"-"&INDEX($A$2:$G$2;2):INDEX($A$2:$G$2;ANZAHL($A3:$G3)-1);"";"");"");;-2))))}
16	C	3	567-3	6	07.08.2016
17	D	3	567-4	4	07.08.2016		C9[:E22]: {=MTRANS(VSplit(VJoin(I3:I6);;1))}
18	B	4	444-2	2,2	12.08.2016
19	C	4	444-3	2,3	12.08.2016		I9:I11[;J9:K11]: {=MTRANS(VSplit(GLÄTTEN(VJoin(WENNFEHLER(INDEX($A3:$G3;1;WAHL
20	D	4	444-4	1,4	12.08.2016		(SPALTE(A1);1;2;ANZAHL($A3:$G3))):INDEX($A3:$G3;1;WAHL(SPALTE(A1);1;ANZAHL($A3:
21	E	4	444-5	3,5	12.08.2016		$G3)-1;ANZAHL($A3:$G3)))&WAHL(SPALTE(A1);"-"&INDEX($A$2:$G$2;2):INDEX($A$2:$G$2;
22	F	4	444-6	2,6	12.08.2016		ANZAHL($A3:$G3)-1);"";"");"");;-2));;1))}

Ich verzichte auf das Hochladen einer komplettierten BspDatei, da das für mich erhöhten Aufwand bedeutet und ich doch davon ausgehe, dass du das anhand der Darstellung und der BspDatei selbst hinbekommst. Beachte beim Erstellen der Fmln bitte, dass zuerst die Hilfszellen angelegt wdn müssen. Ihre (grauen) Fmln sind singulare MatrixFmln, d.h., jede einzelne umfasst nur diese eine Zelle. Die schwarzen Fmln (im grünen ErgebnisBereich) verbinden die HilfszellenErgebnisse spaltenweise, sind also spaltenorientierte (plurale) MatrixFmln, d.h., jede erzeugt stets eine ganze Spalte* der ErgebnisTabelle, die dann auch vor Eintrag der Fml in ihre jeweils 1.Zelle ausgewählt wdn muss. Die Eingabe aller MatrixFmln wird bekanntlich nicht nur mit [enter], sondern bei gleichzeitig gedrückten Tasten [strg][umsch] abgeschlossen.
Die blauen Fmln dienen nur als Bsp für die Auswertung einer einzigen Zeile, also die Grundlage der komplexeren Fmln. Hier kann man dann auch sehen, dass die Xl-Steuerung einen einzelnen ErgebnisWert einer MatrixFml auf den ganzen markierten Bereich ausdehnt. Da dieser Effekt auch in den ZusammenfassungsFmln der Hilfszellen wirksam ist, konnte er zur Vervielfachung der nur einzeln vorliegenden Werte genutzt wdn.
* Falls in der ErgebnisTab unbedingt EinzelWerte erzeugt wdn sollen, muss die 1.Fml entweder =INDEX(VSplit(VJoin(I$3:I$6);;1);ZEILE(A1)) oder =Splint(VJoin(I$3:I$6);;ZEILE(A1)) lauten. Die ebenfalls im Archiv enthaltene UDF Splint (RECHERCHE!) integriert die Auswahl (INDEX) und kann im Ggsatz zu VSplit nur EinzelBezüge bzw -Werte verarbeiten. Die MatrixFml-Form ist bei beiden Varianten nicht erforderlich.
Viel Erfolg! Gruß + schöWE, Luc :-?

Beliebteste Forumthreads (12 Monate)

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread