Excel sverweis weitersuchen Bezug in anderer Tabelle

...komplette Frage anzeigen

2 Antworten

Hallo rookie,
klar, mit dem SVerweis kommst Du hier nicht weiter. Auch die üblicherweise bessere Paarung index(;Vergleich();) führt nur dann zum Ziel, wenn keinerlei neue Artikelnummern eingeführt und andere gestrichen worden sind. Trotzdem hier mal so ein Vorschlag, demonstriert an kleinen Feldern (kann aus Deiner SVerweis-Formel deine Blattanlage nicht rekonstruieren, ist mir auch zu groß, also musst Du umgekehrt meine Anlage nachvollziehen und für Deine Zwecke umschreiben):

Artikel1 stehe in Tab1!G6, -nummer in H6, EAN(leer=>Formel) in i6, der richtige Preis in J6, alles bis Zeile 23. Gleiche Anlage mit EAN-Nummern (und falschem Preis) in Tab2. Lege das zur Vermeidung von Irrtümern am besten mal so an.
Die Formel zum Holen der EAN heißt dann in i6:

=INDEX(Tab2!$I$6:$I$23;VERGLEICH(H6;Tab2!$H$6:$H$23;0)+ZÄHLENWENN(Tab2!$H$6:$H6;H6)-1;1)

kannst Du runterkopieren.
index/Vergleich hat den Vorteil, dass Du Dich auf beliebige einzene Spalten beziehen kannst, es gibt keine Leitspalte, man könnte auch Daten links davon holen. Und man erkennt sofort, von wo man sie herholt. Vor allem aber: man kann rund um das Zielgebiet beliebig navigieren: nach links/rechts durch die Festlegung der Holspalte, nach oben/unten - wie hier erforderlich- durch +/- -Summanden. Hab' ich hier schon oft erklärt, zB in
www.gutefrage.net/frage/excel-zeileninhalt-nach-kriterium-uebernehmen
ganz unten in der AW von Shizovran.

Hast Du aber zusätzliche/fehlende Artikel-Nummern, wirds kompliziert:
Ordne beide Listen nach Artikel-Nummer *) (hoffentlich eine reine Zahl, ggf auch als Label eingetragen, aber nichts gemischtes!) steigend und vergleiche beide Spalten mit der Formel:

=wenn(Tab!1i6=Tab2!i6;"ok";wenn(Wert(Tab!1i6)<Wert(Tab2!i6);"dazu";"weg")

Kopiere diese Formel nur ein Stück weit runter und korrigiere dann sofort durch Zeile einfügen in der einen oder anderen Tabelle die Position, ab eins oberhalb kopierst Du die Formel erneut nach unten bis zur nächsten Auffälligkeit.

Kann bei 20.000Zeilen schon recht lang dauern, aber Du verlangst auch die Korrektur einer ungeeigneten Anlage bzw die Ergänzung noch nicht vorhanden gewesener items!

Der Vollständigkeit halber schreibe ich noch auch eine Matrixformel für i6 hin, aber ob die bei 20.000 Zeilen noch funktioniert, weiß ich nicht und narrensicher ist die auch nicht:

={INDEX(Tab2!$I$6:$I$23;KGRÖSSTE((Tab2!$H$6:$H$23=H6)*(Tab2!$I$6:$I$23<>"")*ZEILE(Tab2!$I$6:$I$23);ZÄHLENWENN(H$6:H$22;H6)-ZÄHLENWENN(H$6:H6;H6)+1)-ZEILE(Tab2!$I$6)+1;1)}

Matrixformeln werden in xl mit Strg+Umsch+Eingabe (Ctrl+Shift+Enter) eingegeben, und zwar ohne die geschweiften Klammern zu schreiben (die entstehen beim Drücken dieser 3 Tasten)
Jetzt drück' ich Dir die Daumen, dass Du zum Erfolg kommst!

*) genereller Hinweis zum Sortieren: Wenn Du vorhast, zurückzusortieren, empfiehlt sich die Anlage einer Spalte mit lfd Nummern in beiden Tabellen, die mitsortiert werden. Nach denen kann man dann bei Bedarf zurücksortieren.
Bei Rückfragen musst Du Dich gedulden, ich bin zZt nur sehr selten online!

Die alten Weiber und die guten Gedanken kommen immer hinterher (war ein Spruch meines Vaters).
Ich erinnere mich, mal eine Liste auf diese Art upgedated (geupdated?) -also aktualisiert- zu haben:
Versieh die alte Liste mit einer Spalte, in der "alt" steht, die neue mit einer Spalte an der gleichen Stelle, in der "neu" steht (oder '12_ und '13_, egal was). Es muss nur in jede Zeile der Liste der gleiche Eintrag rein.
Nun kopierst Du eine der beiden Listen und hängst sie an die andere an.

Dann ordnest Du die Kombiliste nach 1. Artikelnummer und 2. nach alt/neu.

Es stehen jetzt immer zusammengehörige Artikel übereinander, hier alt (mit den EAN-Nrn) oben und neu(mit den richtigen Preisen) unten.
Nehmen wir an, die erste Zeile sei 6. Dann schreib in eine neue Spalte die Formel:
=wenn(h6=h5;i5;i6)
und kopierst das nach unten bis ans Ende der Liste. (Technik s.u.)
Wenn in den Originallisten keine Doppel vorkommen (was Du prüfen musst, s.u.), sind danach ALLE Zeilen mit der richtigen EAN-Nummer gefüllt.

Jetzt kopierst Du die gesamte Spalte mit dieser Formel und überschreibst damit die Original-EAN-Spalte, ABER
NICHT einfach EINFÜGEN,
sondern INHALTE EINFÜGEN, WERTE
.

Jetzt kannst Du neu sortieren nach
1. alt/neu
2. Artikelnummer
und dann alle alt -Zeilen gesammelt löschen.

Mach aber immer ein Backup, falls Du gestört wirst oder sonst was schiefgeht!

Prüfung der Ausgangslisten auf Doppel:
in eine neue Spalte =(h6=h5) * 1
hier muss 0 erscheinen (ich** formatiere** die Zellen immer mit 0;[rot]-0;[Farbe7] — , das lässt die Null und die 1 augenfällig unterscheiden. Bei sehr langen Listen noch in zB Z2 (die Formel stehe in Spalte Z) die Formel
=Zählenwenn(Z6:Z25000;1)
das ergibt Die Anzahl evtl noch zu korrigierender Doppel.
-
Kopieren von Einträgen in eine Spalte bei sehr langen Listen bis ans Ende:
Cursor auf zu kopierende Zelle setzen, zB K6. kopieren.
Strg gedrückt halten, mit Pfeiltaste Markierung erweitern bis zu einer garantiert lückenlos gefüllten Spalte.
Bei weiterhin gedrückter Strg-Taste Ende, nach unten(Pfeil).
Die Markierung geht jetzt bis zur letzten Zeile.
Bei weiterhin gedrückter Strg-Taste mehrmals den nach-rechts-Pfeil betätigen, bis die Markierung nur noch die eine, gewünschte Spalte umfasst.
einfügen (mit Kontextmenü oder -sicherer- mit strg+v).
fertig, egal ob 200 oder 200.000 Zeilen.
probier alles erst mal an kleinen, überschaubaren Listen aus!

0
@Iamiam

Hallo lamiam, sorry für die späte Rückmeldung.. war etwas im Stress. Danke schon mal für die 2 Lösungsvorschläge. Werde im laufe der Woche mal probieren was da besser passt und geht (bzw. ob ich alles richtig verstanden habe). Bin mal gespannt, da mein Wissen hier erstmal am Ende war. Werde danach berichten.....

0

Ich habe das mit folgendem Code hinbekommen:

Sub EAN_holen()
    Const Trenner = ", "
    Dim ZeileQuelle, ZeileZiel As Integer
    ZeileQuelle = 2
    ZeileZiel = 1

    While Tabelle3.Cells(ZeileQuelle, 1) <> ""
        If Tabelle3.Cells(ZeileQuelle, 1) = Tabelle3.Cells(ZeileQuelle - 1, 1) Then
            If Tabelle1.Cells(ZeileZiel, 2) <> "" Then Tabelle1.Cells(ZeileZiel, 2) = Tabelle1.Cells(ZeileZiel, 2) + Trenner
        Else
            ZeileZiel = ZeileZiel + 1
            Tabelle1.Cells(ZeileZiel, 1) = Tabelle3.Cells(ZeileQuelle, 1)
        End If
        Tabelle1.Cells(ZeileZiel, 2) = Tabelle1.Cells(ZeileZiel, 2) + Tabelle3.Cells(ZeileQuelle, 2)
        ZeileQuelle = ZeileQuelle + 1
    Wend
End Sub

Öffne dazu den Visual-Basic-Editor und hinterlege den Code unter "Module". Wenn dort noch kein Modul hinterlegt ist, kannst Du ein neues Modul über das Menü "Einfügen" einfügen. In Excel 2002 erreichst Du den VB-Editor über Extras->Makro.

Den Code musst Du natürlich anpassen, z.B. Tabellennamen und Spaltennummern. Teste das erst einmal mit einer Tabelle, die im schlimmsten Fall kaputt gehen kann.

Wichtig: Deine Quelltabelle muss nach Artikelnummer und EAN sortiert sein. Ansonsten funktioniert das nicht. Das kann man zwar anders programmieren, würde dann aber dazu führen, dass die Tabelle zigmal durchlaufen werden müsste. Ich weiß ja nicht, wie lang Deine Tabelle ist - das könnte dann etwas dauern. ;-)

Hallo are2705, danke für das Script.. hab das leider noch nicht gemacht (Macros etc.) aber probiere es auf jeden Fall mal aus... Dauert was da im Moment wenig Zeit, lasse aber wissen welche Lösung am Besten für mein Wissen :o( geeignet war..

0

Was möchtest Du wissen?