Office: (Office 2016) Transponieren/Auflisten aller Werte einer Zeile

Hallo zusammen,
ich habe mir diverse Tutorials angesehen, allerdings keines für meinen Anwenungsfall gefunden...
Meine Herausforderung ist folgende:
Die Ausgangslage ist eine riesige Datei mit individuellen Zeilen diverser Informationen, eingetragen je Spalte.

Also zum Beispiel:
Mensch1 | Apfel | Birne | Zitrone | |
Mensch2 | Apfel | | Zitrone |Paprika |
Mensch3 | | Birne | | |Banane
...

Der Wunsch ist, daraus eine Liste folgender Art zu generieren:
Mensch1 | Apfel
Mensch1 | Birne
Mensch1 | Zitrone
Mensch2 | Apfel
Mensch2 | Zitrone
Mensch2 | Paprika
Mensch3 | Birne
Mensch3 | Banane
...

Die Einträge der Zeile sollen also untereinander aufgelistet werden (transponiert), aber jeweils den Merkmalsträger (MenschXY) voranstehend haben.
Außerdem sollen dabei leere Felder ignoriert werden.

Wonach sucht man da? Auflisten einer Zeile hat mich zum Begriff "Transponieren" gebracht.
VLOOKUP oder INDEX erscheint mir auch noch einigermaßen nachvollziehbar.
Aber wie bringe ich Exel dazu, eine Liste zu erstellen und so lange Mensch1 zu listen, bis in der Zeile keine Werte mehr gefunden werden und dann darunter eigenständig für Mensch2 zu listen, für Mensch3, usw...?

Über hilfreiche Stichworte würde ich mich freuen!
Vielen Dank.

 

Hallo,

ich habe in der beiliegenden Excel-Datei eine ARRAY/Matrix-Funktion in ein allgemeines Codemodul eingefügt. Damit kann man deine gewünschte Datenumwandlung durchführen. Da du einen großen Datenbestand erwähnt hast, dürfte es nicht sinnvoll sein, eine komplexe Formel mit Arbeitsblattfunktionen einzusetzen.

Deine oben angeführten Vorgabedaten befinden sich im Bereich A1:F3 und
die ARRAY-Funktion im Bereich C18:D34. Dieser Bereich enthält die Formel {=Liste2Datensätze(A1:F3)}

Diese ARRAY-Formel wird folgendermaßen eingegeben:
1) Zuerst wird der gewünschte Zellbereich markiert (zB.: C18:D34)
2) In die Formeleditierzeile wird dann eingegeben: =Liste2Datensätze(A1:F3), wobei A1:F3 den Bereich mit den Quelldaten darstellt.
3) Zum Abschließen der Formeleingabe wird die Tastenkombination Strg+Umschalt+EINGABE gedrückt, sodass hinterher die Formel in geschweiften Klammern eingeschlossen erscheint.