Inhalte 2er Tabellen vergleichen, Unterschiede im Datensatz hervor

[Robert Feldmann]

Hallo Sebastian,

evtl. hilft schon eine (oder zwei) Formel.

Annahmen:
Blatt "Soll"
Spalten A B C D E
Artikel Nr. Artikel-Bez Lager Bestand-Soll Diff
(Formel)

Blatt "Ist"
Spalten A B C D E
Artikel Nr. Artikel-Bez Lager Bestand-Ist Diff
(Formel)

Angenommen es gibt ein Blatt "Soll" und ein Blatt "Ist" könntest Du mit
der Formel SVERWEIS(A2;Ist!A:D;4;FALSCH) im Blatt "Soll" nachsehen ob
die Artikelnummer aus A2 im Blatt "Ist" irgendwo in Spalte "A" vorkommt
und durch die Angabe der Spaltennummer in der die Menge (hier 4) steht
die dazugehörige Menge anzeigen lassen.

So könnte in Blatt Soll z.B. =D2-SVERWEIS(A2;Ist!A:D;4;FALSCH) stehen
und in Blatt "Ist" =D2-SVERWEIS(A2;Soll!A:D;4;FALSCH)

Die Fälle, in denen im Blatt ist der Artikel nicht vorkommt werden durch
#NV dargestellt. Das könnte man auch noch per Formel abfangen
=istfehler() aber eigentlich reicht das so.

Guten Abend,
ich bin völliger VBA leihe und muss aber folgendes fertig bringen.
Gegeben sind 2 Tabellen.
Aufbau ca. Artikel Nr.; Artikel-Bez; Lager; Bestand-Soll bzw. Bestand-Ist,
In der einen Tabelle stehen eben zu den Artikelnummern Bestände, die laut
Warenwirtschaftssystem da sein sollten.
In der anderen Tabelle stehen zur Artikelnummer die Bestände die tatsächlich
da sind (laut Inventur).
Nun mein Part. Ich soll eventuelle Abweichungen herrausfinden und
hervorheben.
Wie mache ich das am besten?
Inventurlisten unvollständig, d.h. wenn Artikel nicht auf Lager dann auch
nicht in Liste. Kann aber in Liste aus Warenwirtsschaftssystem stehen.
Ausgabe in 3. Excelliste (nach Möglichkeit)
es handelt sich ca. um 25000 Datensätze.
Wer mir helfen kann, meldet sich bitte.
Danke im vorraus.

[Rudi]

Hallo Robert

eine tolle Lösung, wie ich finde.

Ich habe deine Formel mal in ein Makro gepackt und eine 3.Tabelle mit
dem Vergleich gefüllt, so wie es Sebastian (wörtlich genommen) haben
wollte. (mich hat interessiert, wie schnell eine Schleife mit der
Formel läuft. Dann habe ich 20000 Datensätze ausgewertet und bin ganz
erstaunt, dass das Ganze nur etwa 1 Sekunde dauert)

Sub Inventur()
Dim LastRow As Long, iRow As Long

With Worksheets("Ist")
LastRow = .[A65536].End(xlUp).Row
.Range("A2:C" & LastRow).Copy
End With
With Worksheets("Inventur")
.Paste Destination:=.Range("A2:C" & LastRow)
Application.CutCopyMode = False
LastRow = .[A65536].End(xlUp).Row
.Cells(1, 5) = "Differenz"
For iRow = 2 To LastRow
.Cells(iRow, 5) = Cells(iRow, 4) -
Application.WorksheetFunction.VLookup(.Cells(iRow, 1),
Worksheets("Soll").Columns("a:d"), 4, False)
Next
End With
End Sub

Gruß Rudi

[Sebastian Günther]

Post by Rudi
Hallo Robert
eine tolle Lösung, wie ich finde.
Ich habe deine Formel mal in ein Makro gepackt und eine 3.Tabelle mit
dem Vergleich gefüllt, so wie es Sebastian (wörtlich genommen) haben
wollte. (mich hat interessiert, wie schnell eine Schleife mit der
Formel läuft. Dann habe ich 20000 Datensätze ausgewertet und bin ganz
erstaunt, dass das Ganze nur etwa 1 Sekunde dauert)
Sub Inventur()
Dim LastRow As Long, iRow As Long
With Worksheets("Ist")
LastRow = .[A65536].End(xlUp).Row
.Range("A2:C" & LastRow).Copy
End With
With Worksheets("Inventur")
.Paste Destination:=.Range("A2:C" & LastRow)
Application.CutCopyMode = False
LastRow = .[A65536].End(xlUp).Row
.Cells(1, 5) = "Differenz"
For iRow = 2 To LastRow
.Cells(iRow, 5) = Cells(iRow, 4) -
Application.WorksheetFunction.VLookup(.Cells(iRow, 1),
Worksheets("Soll").Columns("a:d"), 4, False)
Next
End With
End Sub
Gruß Rudi

Ich verstehe zwar selbst den Quelltext nicht, werde es aber mal versuchen.
Ich gehe davon aus, das bei With Worksheets meine Dateinamen reinkommen und
das sich natürlich alle 3 Listen dann im selben Verzeichniss befinden.

Oder?

[Sebastian Günther]

Ich hab ein Problem, da ich wie gesagt den Quelltext nicht ganz verstehe.

In der großen Liste aus der Warenwirtschaft steht die Art_Nr in Spalte A und
die Menge in Spalte G.
Dazwischen viele Infos die anscheinenend nur die Leute vom Einkauf brauchen.
In der Inventurliste sieht es so aus das Artikel Nr. in Spalte G und die
Menge in Spalte K steht.
Macht mir das Probleme?

MfG

S. Günther

[Herbert Taferner]

Hallo Sebastian,

Post by Sebastian Günther
Ich hab ein Problem, da ich wie gesagt den Quelltext nicht ganz verstehe.
In der großen Liste aus der Warenwirtschaft steht die Art_Nr in Spalte A und
die Menge in Spalte G.
Dazwischen viele Infos die anscheinenend nur die Leute vom Einkauf brauchen.
In der Inventurliste sieht es so aus das Artikel Nr. in Spalte G und die
Menge in Spalte K steht.
Macht mir das Probleme?

du hast offensichtlich mehr als ein Problem,
so wie du schreibst sind deine Daten nicht in verschiedenen Tabellen
sondern in verschiedenen Dateien

schreib einfach einmal genau den Ausgangspunkt

Datei1 Name ???? (Warenwirtschaft)
Tabelle Name ????
Spalte A Spalte G
Art Nr. Stückzahl

Datei2 Name ????? (Inventurliste)
Tabelle Name ?????
Spalte G Spalte K
Art Nr. Stückzahl

Ausgabe z. B. nach
Datei3 Name ????
Tabelle Name ????
Spalte A Spalte B Spalte C Spalte D
Art Nr. Stückzahl Stückzahl Differenz
von Datei1 von Datei2

wenn du solch relevante Angaben von Anfang an machst,
dann muss sich der dir helfende nur einmal Gedanken machen

mfg Herbert

[Sebastian Günther]

Tschuldigung. Hatte bis zuletzt den unterschied zwischen Tabelle und Mappe ne
bemerkt.

Also ich habe eine Datei aus der Warenwirtschaft.
Diese nennen wir Soll.
Spaltenüberschriften:
TEILENR;BESCHAFFSCHL;ENTNAHMESCHL;MENGENSCHL;TEILEBEZ1;Lager; Bestand
Das was für uns wichtig ist wäre ja Spalte A Teilenr. und Spalte G Bestand.

Dann habe ich eine Datei Inventur.
Spaltenüberschriften:
Teilenummer;Teilebezeichner;Mengeneinheit;Lagerplatz;Anzahl;Listennummer ;Positionsnummer
Für mich wieder nur Interessant Teilenummer (Spalte G) und Anzahl (Spalte K).

Mein Ziel ist es, die Liste Inventur mit der Liste Soll zu vergleichen.
In der 3. Tabelle sollte stehen:
Spalten A:G aus Liste Soll, Spalten H könnte sein Ist (also Anzahl aus
Inventurliste), und Spalte I sollte auf Differenzen hinweisen. Wenn Werte
dann leer lassen.

Ich hoffe ich konnte mein Problem nun schildern. Fällt mir gar ne so leicht.

Danke schonmal für eure Mühe.

MfG

S. Günther

[Herbert Taferner]

Hallo Sebastian,

Post by Sebastian Günther
Tschuldigung. Hatte bis zuletzt den unterschied zwischen Tabelle und Mappe ne
bemerkt.

das passiert öfter

Post by Sebastian Günther
Also ich habe eine Datei aus der Warenwirtschaft.
Diese nennen wir Soll.
TEILENR;BESCHAFFSCHL;ENTNAHMESCHL;MENGENSCHL;TEILEBEZ1;Lager; Bestand
Das was für uns wichtig ist wäre ja Spalte A Teilenr. und Spalte G Bestand.
Dann habe ich eine Datei Inventur.
Teilenummer;Teilebezeichner;Mengeneinheit;Lagerplatz;Anzahl;Listennummer
;Positionsnummer
Für mich wieder nur Interessant Teilenummer (Spalte G) und Anzahl (Spalte K).
Mein Ziel ist es, die Liste Inventur mit der Liste Soll zu vergleichen.
Spalten A:G aus Liste Soll, Spalten H könnte sein Ist (also Anzahl aus
Inventurliste), und Spalte I sollte auf Differenzen hinweisen. Wenn Werte
dann leer lassen.
Ich hoffe ich konnte mein Problem nun schildern. Fällt mir gar ne so leicht.

versuchen wir es mal ganz einfach,
hier musst du dann die Dateinamen und Tabellennamen die du bis jetzt
ja beharrlich verschwiegen hast ersetzen
hier z. B.
Soll.xls Tabelle1 ist die Warenwirtschaftsliste
Ist.xls Tabelle1 ist die Inventurliste

nimm jetzt eine neue Tabelle
in A1 schreibst du
=[Soll.xls]Tabelle1!A1
dann nimmst du die Formel und ziehst sie bis G1 und solange noch alles markiert
ist
eine Zeile nach unten
in H1 schreibst du Inventur Ist
in I1 schreibst du Differenz
in H2 schreibst du =SVERWEIS(A2;[Ist.xls]Tabelle1!G:K;5;0)
in I2 schreibst du =WENN(G2=H2;"";H2-G2)

nun markierst du A2 bis I2 und wählst im Menü
Format bedingte Formatierung im erscheinenden Fenster stellst du
links von Zellwert ist um auf Formel ist und schreibst ins Feld
rechts daneben =I$2<>""
mit dem Button Format darunter wählst du z. B. Schriftfarbe Rot
damit werden alle Zeilen wo ein ungleicher Wert steht rot gefärbt

nun ziehst du die markierten Zellen A2 bis I2 soweit nach unten
wie nötig

mfg Herbert

[Herbert Taferner]

Hallo Sebastian,

da hat sich ein Fehler eingeschlichen,
in der Formel der bedingten Formatierung

statt =I$2<>""

schreibe =$I2<>""

mfg Herbert

[Robert Feldmann]

Hallo Rudi,

Post by Rudi
Hallo Robert
eine tolle Lösung, wie ich finde.

Danke für die Blumen.

Post by Rudi
Ich habe deine Formel mal in ein Makro gepackt und eine 3.Tabelle mit
dem Vergleich gefüllt, so wie es Sebastian (wörtlich genommen) haben
wollte. (mich hat interessiert, wie schnell eine Schleife mit der
Formel läuft. Dann habe ich 20000 Datensätze ausgewertet und bin ganz
erstaunt, dass das Ganze nur etwa 1 Sekunde dauert)

Da bin ich auch überrascht. Hätte ich mit mehr gerechnet. Allerdings
habe ich auch nicht 20000 Datensätze verwendet, mir ging es mehr um das
Prinzip. Und die Sache mit der eigenen Tabelle bzw. Mappe, die habe ich
nicht als Bedingung gesehen, sondern eher als Definitionsproblem. ;-)

Noch ein Hinweis an Sebastian, evtl. wäre es Sinnvoll den Kollegen für
die Inventur die Liste aus der Warenwirtschaft zur Verfügung zu stellen,
dann hast Du die Mengen gleich da stehen wo Du sie benötigst und
brauchst nur noch eine winzige Formel.

bis dann
Robert