Sverweis mit mehreren Suchkriterien und Ergebnisse

Bild

Betrifft: Sverweis mit mehreren Suchkriterien und Ergebnisse
von: Rene
Geschrieben am: 29.06.2015 10:48:27

Hallo,
Ich habe ein kleines Problemchen bei der Arbeit, welches sich glaube ich nur via VBA lösen lässt. Lasse mich aber auch gerne eines anderen belehren. Leider habe ich kaum VBA-Kenntnisse.
zum Problem:
Ich habe eine Excel-Datei mit 2 Tabelllenblättern in denen jeweils eine Tabelle mit etwa 20 tausend Zeilen steht. Sowohl in Tabelle1 als auch in Tabelle2 habe ich die Spalte "Artikelnummer" mit den jeweiligen Artikelnummern sowie die Spalte "Vorgangsnummer" ich möchte nun nach diesen 2 Kriterien die ich in Tabelle1 Zeile1 entnehme die dazugehörigen informationen aus Tabelle 2 suchen (mehrere Ergebnisse möglich)
Jetzt kommt der komplizierte Teil. Die Ergebnisse die mehrfach vorkommen müssen in neue Zeilen in Tabelle 1 eingefügt werden.
Hier ist noch eine Beispieltabelle zur berdeutlichung des Problems:
https://www.herber.de/bbs/user/98538.xlsx
Vielen Dank schonmal im Vorraus für die Hilfe.
Mit freundlichen Grüßen,
Rene

Bild

Betrifft: nachgefragt ...
von: der neopa C
Geschrieben am: 29.06.2015 11:14:43
Hallo Rene,
... wenn das nur eine einmalige Angelegenheit ist, gäbe es eine einfache und schnelle Lösung ganz ohne Formeln und VBA, vorausgesetzt Deine Angaben z.B. F5:G5 in der Tabelle "Sollzustand" wäre in der Tabelle1 in E4:F4 schon da und analog auch für die anderen Daten?
Ist das der Fall?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ...
von: Rene
Geschrieben am: 29.06.2015 11:27:43
Vielen Dank erstmal für die schnelle Antwort Werner.
Ich hoffe ich habe dich richtig verstanden, die Tabelle "Sollzustand" sollte das Endprodukt wie ich es gerne hätte darstellen ..also quasi Tabelle1 nachdem man Die Infosätze aus Tabelle 2 integriert hat. aber ja es handelt sich um eine einmalige aktion die ich aber nicht per hand durchführen kann, weil es in der Orginaldatei (20000Zeilen und 40 Spalten) zu lange dauern würde..

Bild

Betrifft: eine mögliche Variante wäre ...
von: der neopa C
Geschrieben am: 29.06.2015 11:49:01
Hallo Rene,
... wäre unten stehender Vorschlag.
Doch wie bereits geschrieben: "...vorausgesetzt Deine Angaben z.B. F5:G5 in der Tabelle "Sollzustand" wäre in der Tabelle1 in E4:F4 schon da und analog auch für die anderen Daten"

Kopiere den gesamten Inhalt der Tabelle 1 in eine dritte neue Tabelle und kopiere unten drunter den gesamten Inhalt aus Tabelle 2. Formatiere den gesamten neuen Datenbestand mit der Funktion "Als Tabelle formatieren" und sortiere diese dann nach "Artikelnummer und Vorgangsnummer"
Dann hast Du gleich alle Daten zusammen.
Oder habe ich da Dein Anliegen falsch interpretiert und Du willst wirklich nur definierte Teile aus Tabelle 2 in Tabelle1 übernehmen? Dann käme nur eine VBA-Lösung in Frage. Das schreibe dann bitte in Dein Antwortbetreff.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Rene
Geschrieben am: 29.06.2015 12:56:28
Hallo Werner,
Tut mir leid, du hast recht ich habe vergessen zu erwähnen, dass ich wirklich nur die genauen Treffer übertragen will. also nur die Fälle in denen beide Suchkriterien zutreffen..
Ich habe es bereits mit einem Sverweis versucht der etwas umgeschrieben wurde und habe versucht diesen dann auf 2 Kriterien zu beziehen. Klappt aber auch nicht so ganz...
Hier ist der umgeschriebene Sverweis:


Public Function SVERWEIS2(Kriterium As String, _
    Bereich As Range, _
    SuchSpalte As Integer, _
    ErgebnisSpalte As Integer, _
    Optional Unikate As Boolean = True, _
    Optional Trenner As String = ", ") As String
'***********************************************
'Autor: Ransi
'***********************************************
Dim arrTmp
Dim L As Long
Dim Mydic As Object
arrTmp = Bereich
Set Mydic = CreateObject("Scripting.Dictionary")
If Unikate = True Then
    For L = 1 To UBound(arrTmp)
        If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(arrTmp(L, ErgebnisSpalte)) = 0
    Next
    SVERWEIS2 = Join(Mydic.keys, Trenner)
    Else:
    For L = 1 To UBound(arrTmp)
        If arrTmp(L, SuchSpalte) = Kriterium Then Mydic(L) = arrTmp(L, ErgebnisSpalte)
    Next
    SVERWEIS2 = Join(Mydic.items, Trenner)
End If
End Function


Den habe ich dann in der Syntax so angewendet um die 2 Kriterien abzudecken:


=SVERWEIS2(Suchkriterium1;WENN(Suchspalte2=Suchkriterium2;Suchmatrix;““);Suchspalte1;Ergebnisspalte; Wahr;"/")

Das ganze klappt aber leider nicht..Ich kriege ständig die Fehlermeldung"#WERT!".
Entschuldige nochmals die falsche Ausweisung meines Threads.
Grüße, Rene

Bild

Betrifft: mit SVERWEIS() kommst Du hier nicht weiter ...
von: der neopa C
Geschrieben am: 29.06.2015 13:28:03
Hallo Rene,
... ich könnte Dir jedoch eine Formellösung aufzeigen, die Dir z.B. eine Matrix der Zeilenwerte übergibt, die die Treffer enthält. Aus VBA-Lösungen halt ich mich ansonsten außen vor. Deshalb stelle ich den thread auch auf offen. Sollte Dir jedoch mein Formelvorschlag schon weiterhelfen, dann melde Dich einfach noch einmal.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: mit SVERWEIS() kommst Du hier nicht weiter ...
von: Rene
Geschrieben am: 29.06.2015 13:31:14
Hallo Werner,
über den Ansatz habe ich es auch schon probiert.. kam dann aber leider nicht weiter.. vielen Dank für deine Hilfe ich hoffe einfach mal auf noch einen bereitwilligen Helfer.
Grüße, Rene

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Claus
Geschrieben am: 29.06.2015 13:36:03
Der SVERWEIS bezieht sich ja nur auf ein Kriterium.
Jedoch: Du könntest in beide Tabellen eine Hilfsspalte einfügen, in der du in etwa folgendes machst: =verketten(Krit1;Krit2) also die jeweilige Spalte und dann runterkopieren.
Jetzt kannst du deinen SVERWEIS jeweils auf diese Hilfsspalte beziehen... dann könnte es klappen.
Grüßle Claus

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Rene
Geschrieben am: 29.06.2015 13:51:11
Hallo Claus,
Vielen Dank für die Antwort. Das zweite Kriterium habe ich versucht über die Wennfunktion einzubauen aber deine Idee ist auch super und klappt jetzt auch!
Jetzt muss ich nurnoch herausfinden wie ich es schaffe automatisch Zeilen einzufügen und die Mehrfachergebnisse in diese Leerzeilen einzutragen...
Vielen lieben Dank!

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Claus
Geschrieben am: 29.06.2015 14:05:16
Hallo Rene,
Dabke für die nette Rückmeldung - prima wenns geholfen hat. Der Rest geht sicher mit VBA - und da bin ich leider nicht so gut, deshalb verabschiede ich mich hier...
Grüßle Claus

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Daniel
Geschrieben am: 29.06.2015 13:38:25
HI
sieht für moch so noch aus:
1. geiche die Spalten an, so dass in gleiche Inhalt ein gleichen Spalten stehen (dh in Tabelle2 eine Leerspalte für den Infosatz 1 einrichten)
2. kopiere Tabelle2 unter die Tabelle1
3. sortiere das ganze nach Artikel- und Vorgangsnummer
Gruß Daniel

Bild

Betrifft: AW: eine mögliche Variante wäre ...
von: Daniel
Geschrieben am: 29.06.2015 13:38:25
HI
sieht für moch so noch aus:
1. geiche die Spalten an, so dass in gleiche Inhalt ein gleichen Spalten stehen (dh in Tabelle2 eine Leerspalte für den Infosatz 1 einrichten)
2. kopiere Tabelle2 unter die Tabelle1
3. sortiere das ganze nach Artikel- und Vorgangsnummer
Gruß Daniel

Bild

Betrifft: offensichtlich hast Du nicht gelesen ...
von: der neopa C
Geschrieben am: 29.06.2015 13:42:40
Hallo Daniel,
... was ich Rene geschrieben habe und auch nicht, was Rene darauf geantwortet hat.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: offensichtlich hast Du nicht gelesen ...
von: Daniel
Geschrieben am: 29.06.2015 14:01:24
Hi
nö, hab ich nicht gelesen.
aber man kann ja in der Tabelle2 vorher diejenigen Zeilen, die nicht übernommen werden sollen, voher löschen.
Zum Kennzeichnen kann ZählenWenns verwenden.
Gruß Daniel

Bild

Betrifft: AW: offensichtlich hast Du nicht gelesen ...
von: Rene
Geschrieben am: 29.06.2015 16:17:30
Leider ist das bei über 20000 Zeilen etwas zu viel arbeit...
Trotzdem danke für die Antwort ..

Bild

Betrifft: AW: offensichtlich hast Du nicht gelesen ...
von: Daniel
Geschrieben am: 29.06.2015 18:17:46
HI
mit Zählenwenns markieren und dann löschen.
wenns von der Datenmenge her zuviel für Zählenwenns ist, dann:
per Formel Vorgangsnummer und Artikelnummer zu einem Begriff zusammenfassen
Tabelle nach diesem Zusammengefassten Suchbegriff sortieren.
Per SVerweis mit 4. Parameter = Wahr überprüfen, ob der Suchbegriff in der anderen Tabelle vorhanden ist (Suchwert = Rückgabewert).
Zum löschen eine Formel in einer Hilfsspalte erstellen, die die Werte die gelöscht werden müssen mit 0 markiert und die die stehenbleiben müssen mit der aktuellen Zeilennummer.
Dann mit Duplikate-Entfernen die Zeilen mit 0 löschen (in die Überschriftenzeile der Hilfsspalte kommt ebenfalls die 0)
Wenn man das Prinzip mal verstanden hat, hat man auch ohne Makro die grössten Listen im Griff und kann sie schnell bearbeiten und zusammenfassen.
Makro ist dann nur notwendig, wenn eine wiederholte Bearbeitung erforderlich ist.
dieses Makro ist dann aber relativ einfach erstellt, weil man ja nur noch diese Schritte nachprogrammieren muss und man dabei vom Recorder unterstützt wird.
Gruß Daniel

Bild

Betrifft: AW: offensichtlich hast Du nicht gelesen ...
von: Rene
Geschrieben am: 29.06.2015 20:23:51
Vielen Dank!
Ich werde es gleich morgen bei der Arbeit ausprobieren aber es klingt schonmal sehr gut. Danke nochmal :-)

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Sverweis mit mehreren Suchkriterien und Ergebnisse"