S-Verweis mit Wenn-Und, Transponieren????

Hallo zusammen,

ich habe folgendes Problem (und es ist schon kompliziert es überhaupt verständlich zu erklären):

Wie in der Abbildung zu sehen, habe ich mehrer Klassen in Spalte B (bis zu 15), jedoch variieren die Anzahl der Einträge von 10 bis 15.

Jetzt soll die Tabelle transponiert werden, so dass sie in etwa so aussieht:

fbnr | 11 | 12 | 13 | 14 | 15 | ...
8314405 | 3190 | 1578 | 258 | ... | ...
8314517 | 247 | 214 | 1258 | ... | ...
8314931 | 125 | 7890 | 6987 | ... | ...

Die Idee ist, jeweils in der Zeile, in der der fbnr-Wert zum ersten Mal auftaucht den jeweiligen Wert für 11, 12, 13, etc. zu schreiben und die restlichen Zeilen danach zu löschen.

Meine Versuche mit Wenn-Dann und S-Verweis gingen allesamt schief, da die Anzahl der Klassen in Spalte B variiert, so dass nicht alle Werte in der ersten, jeweiligen fbnr-Zeile stehen.

Hat jemand eine Idee wie ich mein Problem elegant lösen kann?

Vielen Dank
R

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 292247

Url: https://administrator.de/forum/s-verweis-mit-wenn-und-transponieren-292247.html

Ausgedruckt am: 26.12.2024 um 17:12 Uhr

6 Kommentare

Neuester Kommentar

Guckst du z.B. hier:
Excel Zeilen in eine Zeile anzeigen

den Code dort ein bisschen angepasst so dass es nicht in eine Zelle zusammengefasst wird sondern stattdessen als neue Zellen

Sub MergeDuplicates()
    Dim ws As Worksheet, rngStart As Range, rngEnd As Range, rngCurrent As Range
    ' Erstes Tabellenblatt referenzieren  
    Set ws = Worksheets(1)
    'Startzelle der Daten festlegen  
    Set rngStart = ws.Range("A2")  
    ' Zelllendbereich ermitteln  
    Set rngEnd = rngStart.End(xlDown).Offset(0, 1)

    ' Bereich zuerst nach Nummern sortieren  
    ws.Range(rngStart, rngEnd).Sort ws.Range("A1")  
    
    'So lange zusammenfassen bis auf eine Zelle keinen Inhalt hat  
    Set rngCurrent = rngStart
    While rngCurrent.Value <> ""  
        If rngCurrent.Value = rngCurrent.Offset(1, 0).Value Then
            rngCurrent.End(xlToRight).Offset(0, 1).Value = rngCurrent.Offset(1, 1).Value
            rngCurrent.Offset(1, 0).EntireRow.Delete
        Else
            Set rngCurrent = rngCurrent.Offset(1, 0)
        End If
    Wend
End Sub

Gruß jodel32

Hallo Jodel,

vielen Dank erstmal aber ich kann mit Codes und Makros gar nichts anfangen und würde das gerne mit einem S-Verweis lösen und dann Copy+Paste das Problem lösen. Meine Kenntnisse reichen nicht aus um den Code einzugeben und ggf. anzupassen.

Hat mir jemand einen Vorschlag wie ich das mit einem S-Verweis löse?

Gruß
R

Hallo @TheDukeOfOGHooD,

Hat mir jemand einen Vorschlag wie ich das mit einem S-Verweis löse?

mit einem reinen S-Verweis klappt das nicht,

Hat jemand eine Idee wie ich mein Problem elegant lösen kann?

aber mit etwas Matrix-Formel-Würze lässt sich das auch elegant lösen. Siehe dazu folgendes Beispiel-Sheet, daran kann man es sich einfacher abschauen.

matrix_transpose_292247.xlsx

Dieses sieht so aus:

Für die Spalten A und B habe ich im Sheet Namen definiert welche in den Formeln Verwendung finden, so dass sie leserlicher werden.
Für die Werte in Spalte A verwende ich den Namen Numbers und die zu erhaltenden Daten in Spalte B den Namen Values.

In Zelle F2 steht dann folgende Matrix-Formel welche im ersten Schritt alle eindeutigen Nummern ohne Duplikate auflistet, und welche man einfach nach unten kopieren kann:

=WENNFEHLER(INDEX(Numbers;VERGLEICH(0;ZÄHLENWENN($F$1:F1;Numbers);0);1);"")

In Zelle G3 steht folgende Formel, welche sich ebenfalls nach rechts und unten kopieren lässt.

=WENNFEHLER(INDEX(Values;KKLEINSTE(WENN($F2=Numbers;ZEILE(Numbers)-MIN(ZEILE(Numbers))+1;"");SPALTE(A$1)));"")

BITTE BEACHTEN: Dies sind Matrix-Formeln. Man muss die Formel mit STRG-SHIFT-ENTER abschließen anstatt nur mit einem "einfachen" Enter. Eine Matrixformel erkennt man in Excel daran das in der Formelzeile die Formel mit geschweiften Klammern eingefasst wird.

Man sollte beachten das das nur für eine begrenzte Anzahl an Daten effizient läuft da Matrix-Formeln einiges an Rechenleistung benötigen.

Eine Makro-Lösung habe ich bei Bedarf aber auch auf Lager:

matrix_transpose_makro_292247.xlsm

Grüße Uwe

Moin colinardo,

im Prinzip ja, aber es passt nicht ganz zur Aufgabenstellung.

Der TO hat 3 Spalten, die er pivotieren bzw. "transponieren" will.
Wenn im Beispiel mal 99 Zeilen vorhanden wären, also
- in $A$2:$A$99 das, was du "Numbers" genannt hast
- in $B$;B99 die LUs, bei dir "Values", die die neuen Spaltenüberschriften darstellen sollen in $G1:$J1
- dann stehen in $C$2:$C$99 die Werte (hab ich Values2 benannt). Die sollen in die neue Matrix einsortiert werden.

Nun denn, wenn die Ausgangssituation ist, dass die Rohdaten nach Spalte A + Spalte B sortiert sind, dann passt deine Formel in den Feldern $F$2:F$99. Lassen wir also so.

Die neuen "Spaltenköpfe" in Zelle $G1 bis irgendwas$1 berechnen wir analog deiner Formel:
$G1;

=Wennfehler(INDEX(Values;VERGLEICH(0;ZÄHLENWENN($G1:G1;Values);0);1);"")

(Als Matrixformel eingeben und nach rechts kopieren sowet nötig)

Den Inhalt der Matrix dann beginnend mit $G$2:

=Wennfehler(INDEX(Values2;VERGLEICH($F2&G$1;$A$2:$A$99&$B$2:$B$99;0);0);"")  

(Als Matrixformel eingeben und nach rechts kopieren)

Dann sollte es noch besser passen.

Grüße
Biber

Zitat von @Biber:
Moin colinardo,

Hallo Biber

im Prinzip ja, aber es passt nicht ganz zur Aufgabenstellung.

wollte damit ja nur den Grundstein für den TO legen

.
Die Formeln sind ja dann, wie du schon demonstriert hast leicht angepasst. Sie sollten nur zeigen wie man eindeutige Werte auflistet und mehrere Ergebnisse zu einer Suche nebeneinander auflistet - das Handwerkszeug eben für diese Aufgabenstellung.

Dann sollte es noch besser passen.

Jepp

, hatte halt den Lerneffekt für den TO vordergründig im Sinn.
Aber jetzt hat er ja alles was er braucht.

Grüße Uwe