Listenvergleich (beidseitig)

Bild

Betrifft: Listenvergleich (beidseitig)
von: Bernd
Geschrieben am: 22.07.2015 08:42:00

Hallo zusammen,
folgendes Problem:
Ich muss 2 Listen miteinander vergleichen, die im Idealfall bzgl. eines Kriteriums (hier Menge z. B.) komplett übereinstimmen können.
Leider ist es aber nun so, dass die Listen voneinander abweichen. Es gibt verschiedene Varianten:
• Quelle 1 hat einen anderen Bestand (Menge) als Quelle 2 für dasselbe Produkt und vice versa.
• Quelle 1 hat einen Bestand für ein Produkt (129 im Bespiel) , Quelle 2 hat das Produkt gar nicht und vice versa:
• Außerdem sollte es eine Option geben, dass man aufgrund eines Kommentars die mögliche Differenz als akzeptabel erachtet, die Abweichung sollte nicht in der Endauswertung moniert (ausgeblendet werden (Kommentar z. B. : „keine Differenz“)
Bisher bilde ich das ganze über Excel und manuelles Autofiltern ab. Da die Originallisten recht umfangreich sind und viele Spalten (auch nicht notwendige) enthalten, ist die Abstimmung wenig elegant und für unbedarfte „Dritte“ eher undurchsichtig. Dadurch dass beide Abstimmungsrichtungen durchgeführt werden sollen (also Quelle1 mit Quelle2 und vice versa) gibt es natürlich Redundanzen, d h. identische Differenzen aus beider Abstimmungsrichtungen.
Nun mein „Wunsch“:
Eine Vereinfachung per VBA wäre mir deutlich lieber und man könnte eine solche Abstimmung auch mal jemand mit geringen Excel-Kenntnissen anvertrauen. Die vorgegebenen Listen sollen soll aufbereitet werden, dass die gewünschte Endauswertung schön übersichtlich und kompakt wird und keine Redundanzen aufweist (Mengendifferenzen tauchen ja doppelt auf, aufgrund er beiden Abstimmrichtungen wie oben schon erwähnt).
Ich habe eine entsprechende Musterdatei angehängt, die alle angesprochenen Varianten enthalten sollte. Zielvorstellung wäre für mich die Darstellung im Sheet „Vergleich(Wunsch).“
Folgendes sollte dabei berücksichtigt werden: Wenn die Menge eines Produktes in Quelle 1 zu Quelle 2 differiert, dann ist das natürlich auch umgekehrt so. Es wäre schön, wenn aber nur eine Zeile im Endvergleich erzeugt wird (ist da die identische Differenz aus Sicht der jeweiligen Abstimmungsausrichtung). In den jeweiligen Quellen gibt es eine Hilfspalte „gemeinsamer Schlüssel“. Im Regelfall entspricht das der Produktnummer. Leider gibt es aber auch ab und an Ausnahmen, die Produktnummern differieren, aber es ist trotzdem das gleiche Produkt. Für diese Fälle würde ich gerne die Hilfsspalte verwenden, wo ich manuell notfalls einen gemeinsamen Schlüssel eintragen kann. Der „technische“ Vergleich sollte sich also primär an der Hilfspalte „gemeinsamer Schlüssel“ orientieren und nicht an der Produktnummer.
Ich hoffe, ich konnte die Zielvorstellung einigermaßen gut beschreiben, das mit den Abstimmungsrichtungen und doppelten Differenzen sollte hoffentlich klar sein.
Mustervorlage:
https://www.herber.de/bbs/user/98996.xlsx
Viele Grüße
Bernd

Bild

Betrifft: AW: Listenvergleich (beidseitig)
von: Michael
Geschrieben am: 25.07.2015 19:34:47
Hallo Bernd,
ich hatte schon mal angefangen, was zu programmieren, aber gehofft, daß sich jemand anderer Deiner Geschichte annimmt - was offensichtlich nicht der Fall ist.
Meiner Meinung nach muß ein Vergleich von zwei Quellen immer dasselbe Ergebnis auswerfen, egal von welcher man ausgeht: d.h., dem gewünschten *einzeiligen* Ergebnis würde ich ein mehrzeiliges vorziehen, aus dem zumindest hervorgeht, welche Produkte in nur einer Datei vorhanden sind und daher nicht direkt verglichen werden können.
Ein weiterer, logischer "Fehler" liegt im Ansatz, und zwar in der Geschichte mit dem "Kommentar", der dazu führt, daß etwaige "kleine" Differenzen nicht angezeigt werden.
Entweder man macht *zwei* Makros, wobei das erste alle Differenzen und das zweite das endgültige Ergebnis anzeigt, wobei der Anwender "zwischendrin" die entsprechenden Kommentare einfügt
*ODER*
man macht *ein* Makro, das dann aber in einer oder beiden Quellen die zusätzliche Angabe benötigt, wann ein Ergebnis "unterdrückt" werden soll und wann nicht, also etwa eine Spalte "Differenz", die entweder leer ist (oder eine 0 enthält) oder einen Wert wie "2", so daß eine Differenz von max. 2 nicht berücksichtigt wird.
Mach Dir da mal Gedanken, und nächste Woche sehe ich zu, daß ich das fertig bekomme.
Schöne Grüße,
Michael
P.S.: Ich habe beim Testen als Allererstes die Formeln des "eindeutigen Schlüssels" durch Werte ersetzt: ein "eindeutiger Schlüssel" ist mir sozusagen zu "wertvoll", um nicht "definitiv festgelegt" zu sein: nur damit Du im Vorfeld darauf Acht gibst - sonst funzt mein Makro nicht richtig.

Bild

Betrifft: AW: Listenvergleich (beidseitig)
von: Bernd
Geschrieben am: 27.07.2015 07:49:34
Hallo Michael.
danke für Deine Rückmeldung. Entgegen meiner sonstigen Erfahrungen mit Anfragen, hat sich diesmal keiner darauf "gestürzt". Es freut mich natürlioh, dass Du mich nicht im Stich lassen möchtest. Ich habe mich in den vergangenen Tagen mit der Materie beschäftigt und mich was "zusammengebastelt", dass durchaus brauchbar ist, vielleicht aber nicht besonders elegant.
Dennoch möchte ich auf meine Mustervorlage zurückkommen und Deine Anmerkungen. Ein Abgleich beider Listen mittels einer Auswertung hat schon seinen Charme! Wie ja schon in der Beschreibung erwähnt, differieren die Produktnummern teilweise, obwohl es sich um das gleiche Produkt handelt, da die Datenzulieferer nicht identisch sind. Deswegen muss ich dann mit einem künstlichen gemeinsamen Schlüssel arbeiten. Das habe ich über Hilfsspalte mit Excel-Boardmitteln teilweise auch automatisiert hinbekommen, sofern die Abweichungen in den Produktnummern einer gewisse Systematik aufweisen.
Deinen Vorschlag, Bagatelldifferenzen erst mit einem 2. Makro auszublenden, halte ich auch für sehr interessant. Das macht das ganze sehr Transparenz und man kann die Schwellen dann später auch ändern.
Erstmal vielen Dank für Deinen Beitrag, ich hoffe es reicht noch diese Woche. Bin ab Freitag quasi für ein paar Wochen im Urlaub....
Viele Grüße
Bernd

Bild

Betrifft: AW: Listenvergleich (beidseitig)
von: Michael
Geschrieben am: 27.07.2015 16:53:58
Hallo Bernd,
das mit zwei Makros halte ich eher für eine Krücke: Transparenz, ja, ok, und es mag sogar übersichtlicher zu programmieren sein.
Die Frage ist nur, ob das dann nicht im Widerspruch steht zu Deiner Anforderung, daß es für "reine Anwender" leicht bedienbar sein soll, denn in dem Fall muß er denn doch "Hand anlegen".
Im Nachhinein ist mir noch eingefallen, daß man ja alternativ absolute Differenzen (wie die genannte "2") oder auch relative, nämlich z.B. 5% verwenden könnte.
Vielleicht wäre es ganz nett, sowohl eine Differenz angeben zu können als auch den Zwischenschritt anzuzeigen.
Ich bastel mal ein bißchen und melde mich später nochmal.
Schöne Grüße,
Michael

Bild

Betrifft: Lösungsvorschlag
von: Michael
Geschrieben am: 27.07.2015 19:41:41
Hallo Bernd,
Tabelle mit Makro anbei: https://www.herber.de/bbs/user/99104.xlsm
Das Ding enthält kein Errorhandling und keine Optimierung (z.B. Bildschirmaktualisierung ausschalten), tut aber brav:
Im ersten Schritt werden beide Blätter zusammenkopiert und sortiert und alles, was keine Differenzen aufweist, mit "x" gekennzeichnet. Nach Makroende kann die Spalte mit den x nach Bedarf geändert werden.
Das zweite Makro löscht alles mit x.
Dabei fiel mir auf, daß die Spalten in beiden Tabellen unterschiedlich angeordnet sind. Das erste Blatt wird kopiert, wie es ist, und beim zweiten Blatt kann man im String bisS angeben, wohin die Spalten A-D der Quelle in die Auswertung kopiert werden. Wenn sich das öfters ändern sollte, könnte man den Wert für bisS irgendwo in der Tabelle hinterlegen (z.B. Auswertung!W2), dann kann der Anwender die Reihenfolge ändern, ohne in VBA arbeiten zu müssen.
Also, teste mal die Buttons!
Happy Exceling,
Michael

Bild

Betrifft: AW: Lösungsvorschlag
von: Bernd
Geschrieben am: 28.07.2015 09:47:09
Hallo Michael,
das Makro läuft wirklich gut. Schön schlank und alles in einer Ergebnisübersicht. Ich habe ja selbst auch was gebastelt, aber Endprodukt sind halt 2 Auswertungen und vieles mit Filtern und Makrorekorder, also nicht sehr elegant.
Was mir noch so auffiel: Wenn die Listen Formeln enthalten sollten (dieser gemeinsame Schlüssel ist sowas), dann ist das Makro empfindlich. Ich kann das zwar über eine Hilfsspalte umschiffen, wo wirklich nur Werte drinstehen, aber natürlich unschön. Kann man das eventuell in VBA abfangen? Ich setze die Anfrage noch mal auf "offen", vielleicht weißt Du da noch eine Antwort!
Auf jeden Fall schon mal besten Dank für Deine Mühe, das Warten hat sich gelohnt! Ich werde das Makro natürlich noch an meine Echtdaten anpassen müssen, aber das sollte ich hinbekommen.

Bild

Betrifft: AW: Lösungsvorschlag
von: Michael
Geschrieben am: 28.07.2015 15:21:01
Hallo Bernd,
der Knackpunkt liegt beim einfachen "Kopieren" der Spalten; man muß nur die entsprechenden Befehle durch "Kopieren/Werte einfügen" ersetzen. Also prinzipiell: Quelle.copy Ziel.pastespecial
Ich hab's mal eingebaut, jetzt werden die Formeln durch Werte ersetzt: https://www.herber.de/bbs/user/99120.xlsm
Schöne Grüße,
Michael
P.S.: naja, besonders elegant is meine Geschichte auch nicht - Sepp macht es immer viel ausgereifter...
Aber was soll's: If it's stupid but it works, it ain't stupid.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Gesendete Email aus Excel automatisch speichern"