Thema: Übertragung von Daten aus Spalten in Zeile

Hallo, wer kann bitte helfen..? In meiner Tabelle befinden sich Gegenstände untereinander in Spalte A. Jeweils zugordnet sind Zahlen in Spalte B.
Gegenstände in Spalte A können sich unregelmäßig wiederholen mit jeweils anderen zugeordneten Zahlen in Spalte B. Ich möchte diese Daten
jetzt so in einem neuen Tabellenblatt dynamisch anzeigen lassen, dass die Gegenstände ohne Wiederholung in einer Spalte untereinander gelistet werden
und die diesen Gegenständen zugeordneten Zahlen in der Zeile für diesen jeweiligen Gegenstand in neue Zellen rechts (also Spalten) stehen.
Wenn also z.B. in der Ausgangstabelle 1 der Gegenstand Orangen in den Zellen A3,A6,A7, A10 mit zugehörigen Zahlen z.B. 33 (inB3),80(inB6), 15 (inB7)
und 79(in B10) stehen, soll dann in der Tabelle 2 stehen: Orangen (in E3), 33(inF3), 80 (in G3), 15 (in H3), 79 (in I3) usw. Gibt es hierfür eine Formel?
Vielen Dank! https://www.herber.de/bbs/user/178729.xlsx

Hallo Matze,

hier mal meine Lösung mittels Power Query: https://www.herber.de/bbs/user/178730.xlsx

Gruß von Luschi
aus klein-Paris

Hallo Excel-365-Fan's,

so geht's hier:

=LET(a;EINDEUTIG(A2:A11);

     b;REDUCE("";a; LAMBDA(z;aa;WENNFEHLER(VSTAPELN(z;HSTAPELN(aa;ZUZEILE(FILTER($B$2:$B$11;$A$2:$A$11=aa))));"")));

WEGLASSEN(b;1))

Gruß von Luschi
aus klein-Paris

PS: ist aber auch nicht leichter zu verstehen als die Aggregat-Formel-Lösung von Werner (neopa C)

Hallo Matze,

... für Deine Beispieldatei folgende für z.B. max 998 Datensätze in E2:

=WENNFEHLER(WENN(SPALTE(A2)=1;INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A999)/(ZÄHLENWENN(E$1:E1;A$2:A999)=0);1));1/(1/INDEX($B:$B;AGGREGAT(15;6;ZEILE(B$1:B999)/($A$1:$A$999=$E2);SPALTE(A2)-1))))&"";"")

und diese weit genug nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
aus C the unseen

Allen einen lieben Dank für die Bemühungen und so schnellen Antworten. Sehr hilfreiche Vorschläge! Am Besten für meine Zwecke klappt tatsächlich die Formel von neopa C mit Aggregat. Hätte ich nie hinbekommen. Echt Klasse. darf ich noch kurz ergänzend zum Verständnis fragen, was die Zahlen 15 und 6 in der Formel befehligen? Vielen Dank nochmals.

Habe bei gleicher Sachlage noch ein Problem, wenn es sich bei den zugeordneten Werten nicht um Zahlen, sondern Datumsangaben handelt.
Wie muss ich die Formel ab den Spalten mit dem Datum ändern, damit diese nicht als Zahl, sondern Datum angezeigt werden (Zellenformatierung auf Datum funktioniert nicht). Hier muss sicher noch ein Datumsbefehl (Datwert o.ä. zwischengeschoben werden? Vielen Dank!

Hi.
Lösche das &""
Das macht aus den zurückgegeben Werten Text, der gegen Zahlenformate "immun" ist.

Ohne das bekommst du Zahl als Zahl und kannst das Zahlenformat anwenden.
Gruß Daniel

Herzlichen Dank für die Hilfe!

Hallo Matze,

... mit der 15 als 1. Argument der AGGREGAT()-Funktion wird dieser angewiesen, den kleinsten Wert aus der als 3. Argument definierten Matrix zu finden. Die 6 als 2. Argument sorgt dafür, das dabei Fehlerwerte der Matrix unberücksichtigt bleiben.

Mehr zur AGGREGAT()-Funktion findest Du u.a. hier: https://support.microsoft.com/de-de/office/aggregat-funktion-43b9278e-6aa7-4f17-92b6-e19993fa26df

Gruß Werner
.. , - ...
aus C the unseen

Hi

Aggregat ist keine eigenständige Funktion, sondern dient dazu, "normale" Auswertefunktionen (Summe, Min, Max usw) mit Funktionalitäten auszustatten, die diese standardmäßig nicht haben.
Mit der ersten Zahl wählst du die Funktion aus, mit der du auswerten willst, und mit der zweiten Zahl bestimmst du die zusätzlichen Funktionalitäten.

Aggregat ist sozusagen die Weiterentwicklung von Teilergebnis.


Gib die Formel oder die Funktion Aggregat selber ein (kein Copy-Paste)
Excel zeigt dir dann während der Eingabe in einem kleinen Fenster an, welche Optionen du an dieser Stelle hast und was die Zahlen bedeuten.
Das ist natürlich sehr kurz gehalten, aber du kannst hier auch auf den Funktionsnamen klicken und dann bekommst du die ausführliche Hilfe mit den Erklärungen angezeigt.

Gruß Daniel

Hallo Mathias,

eine Formel für diese Umformung kenne ich nicht, aber mit VBA ist das recht gut lösbar. In solchen Fällen nutze ich einen Code (hier: ungeprüfter Pseudo-Code)

dim DD as object: set DD = CreateObject("Scripting.Dictionary")

for i = 2 to cells(rows.count, 1).end(xlup).row

    if DD.Exists(cells(i,1) then

        dd(cells(i,1) = DD(cells(i,1)) & "|" & cells(i,2)

    else

        dd(cells(i,1) = cells(i,2)

    endif

next i



Cells(2, 5).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))

mfg

Hi

In deiner Excelversion so


In E2:

=WENNFEHLER(INDEX(A:A; AGGREGAT(15;6;ZEILE($A$2:$A$11)/(ZEILE($A$2:$A$11)=VERGLEICH($A$2:$A$11;A:A;0)); ZEILE(A1)));"")

In F2

=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE($A$2:$A$11)/($A$2:$A$11=$E2);SPALTE(A1)));"")

Beide Formeln nach unten ziehen und die Formel in F dann auch nach rechts ziehen, jeweils soweit wie benötigt.

Gruß Danel

Hallo Daniel, vielen Dank. Perfekt!!

Hallo,

eine UDF noch hinten dran:
in allgemeines Modul:

Public Function ArtikelMengen(Bereich As Range)

    Dim oDic As Object: Set oDic = CreateObject("Scripting.Dictionary")

    Dim rng As Range, tmp, arr(), k&, i&, max&

    For Each rng In Bereich.Columns(1).Cells

        oDic(rng.Value) = 0

    Next

    tmp = oDic.Keys

    ReDim arr(1 To oDic.Count, 1 To 1000)

    For Each key In oDic.Keys

        k = k + 1

        arr(k, 1) = tmp(k - 1)

        i = 1

        For Each rng In Bereich.Columns(1).Cells

            If rng.Value = tmp(k - 1) Then

                i = i + 1

                If rng.Offset(, 1).Value > 0 Then arr(k, i) = rng.Offset(, 1).Value

            End If

        Next

        If max < i Then max = i

    Next

    ReDim Preserve arr(1 To UBound(arr), 1 To max)

    ArtikelMengen = arr

End Function

Formel in E2: =ArtikelMengen(A2:B11)

Gruß Uwe

Moin Matze, :-)

... ist eine Möglichkeit mit Power Query: ;-)
https://www.herber.de/bbs/user/178735.xlsx

Es ist mir schon klar, dass du mit Basiskenntnissen in Excel nicht gleich in Power Query einsteigen kannst/willst. ;-)

Es gibt gute Hilfen im Internet zu diesem Thema. Wie bei Formeln reicht es auch hier nicht, nur Copy/Paste zu machen. Es funktioniert - aber bei Änderungen wird es schwierig. ;-)
Wenn dann noch mehr Auswertungen mit den Daten gemacht werden sollen kann PQ seine Stärken ausspielen. ;-)

Ich habe dem Bereich A1:B10 über "Formeln - Definierte Namen - Namensmanager" den Namen "KatWert" vergeben. Damit (nicht nur) kann man in PQ die Daten laden - und dann bearbeiten. ;-)

Servus
Case

Hallo Case,

... mit Deinem vorliegenden PQ-Lösungsvorschlag werden Erweiterungen der Quelldaten nicht automatisch berücksichtigt, also zumindest nicht in meiner XL2016er Version.

Gruß Werner
.. , - ...
aus C the unseen

Moin, :-)

... Themenersteller damit nicht klar kommt, kann er fragen. Ich gehe nicht davon aus, dass die Daten so vorliegen. Du weißt, wie man das mit "INDEX" dynamisch macht. ;-)
Ich hoffe und bete, dass wir nun nicht in eine Endlosdiskussion kommen - also von deiner Seite, für mich ist hier Schluß. ;-)

Servus
Case

Funktioniert das In Excel 2019?
Das hat meines Wissens nach noch nicht die AutoSpill-Funktionalität.

Dh man müsste schon beim Eingeben der Formel wissen, wieviel Zeilen und Spalten sie belegen wird, den ganzen Bereich markieren und STRG+SHIFT+ENTER zur Eingabe drücken

Gruß Daniel

Vielen Dank. Sorry, ich habe doch Office 21...

Hi,

in 2021 sollte es auch so gehen:

https://www.herber.de/bbs/user/178731.xlsx

Gruß

Edgar

Wie schwer kann es eigentlich sein, seine Excelversion zu kennen und hier korrekt einzutragen?

Gerade für dein Problem ließen sich mit den neuen Funktion die ab 2021 vorhanden sind, einfachere Formeln schreiben, die dann ohne VBA auskommen und dir auch die Arbeit abnehmen, herauszufinden, wie viele Zeilen und Spalten du benötigst.

Dann müsstest du dich nicht mit Aggregat rumquälen.

Danke für den Hinweis. Habe 2 Geräte (1x Office 19 und 1 x 21). Die Vereinfachungen in Office21 kenne ich leider nicht, da ich kein Profi bin und beruflich nicht damit zu tun habe. Tut mir leid. Die Tips waren trotzdem hilfreich und ich konnte die Formeln nutzen.

Du musst immer die älteste Version angeben, auf der die Datei verwendet werden soll.

Im Forum nachfragen ist ein guter Weg, die neuen Funktionen Schritt für Schritt kennen zu lernen, vor allem lernst du dann gezielt die kennen, die du brauchst.