2 Spalten in 2 unterschiedlichen Excel Dateien vergleichen?
Ich möchte 2 Spalten in 2 unterschiedlichen Excel Dateien miteinander vergleichen. In beiden Spalten stehen Werte (3-4 stellige Codes). Nun möchte ich in eine der Dateien auflisten welche Werte nicht in der einen Spalte sind und andersrum ?
2 Antworten
Das ganze nennt sich S-Verweis.
http://www.traens.com/tipps/microsoft/sverweis-funktion.html
Sind das getrennte Dateien? Dann müssen beide gleichzeitig geöffnet sein. In eine der beiden dann in etwa:
=ZÄHLENWENN([Mappe1.xlsx]Tabelle1!$B$1:$B$100;A1)
Es wird in der anderen Datei die Spalte B (B1:B100) nach dem Wert aus A1 durchsucht bzw. gezählt, wieviele Treffer es gibt.
0 bedeutet: Der Wert aus A1 ist in der anderen Datei nicht vorhanden. Alles >0 ist vorhanden.
Um Missverständnisse zu vermeiden: Gibt es in jeder Datei nur eine Spalte? Willst Du also vergleichen, welche Werte bspw. aus Spalte A in Datei 1 auch in Spalte B in Datei 2 enthalten sind? Dann reicht die Information vorhanden J/N -> daher der Ansatz mit zählenwenn.
Wenn neben dem Vergleich der Spalten noch Werte aus anderen Spalten übernommen werden sollen, ist die Antwort mit SVERWEIS von webflexer der richtige Weg.
In beiden Fällen musst Du die ermittelten Werte kopieren und als Wert einfügen, da die Formeln nicht funktionieren, wenn nicht beide Dateien geöffnet sind.
Formulieren wir das ganze mal neu: ich habe zwei Dateien: in der einen ist eine Spalte mit Codes (z.B. A3N, 3BE etc.) die im Jahr 2016 relevant waren, nun gibt es in der zweiten Datei, Codes in einer Spalte für das Jahr 2017, die aber auch die Codes aus 2016 beinhalten können, gleichzeitig aber auch neue. Mein Ziel ist es also in der 2017er Datei 2 neue Spalten zu haben: in der einen stehen die NEUEN Codes (die nicht in 2016 vorhanden sind) und in der anderen Spalte alle Codes aus 2016 die nicht mehr in 2017 vorhanden sind.
Ich hoffe ich konnte es verständlicher erklären. Vielen Dank im Voraus.
Ohne Makro, d.h. mit Formeln, geht es nicht ohne Zwischenschritt.
Annahme für mein Beispiel: In beiden Dateien stehen die Codes in Spalte A, ab A1 bis A1000
In die 2017-Datei kommt die Formel
=WENNFEHLER(VERWEIS(A2;[Datei2017.xlsx]Tabelle1!$A$1:$A$17);"")
und diese Formel dann bis zum Ende kopieren bzw. runterziehen.
Die Formel liefert für Codes, die in der 2016er Datei gefunden werden, diesen Code, für alle neuen Codes leer.
Dann musst Du, um das Ergebnis dauerhaft festzuhalten, kopieren:
Einmal die Spalte mit den Formeln und als "Werte" einfügen. Das ist die Spalte für die Codes, die schon 2016 existierten. Dann nach leer filtern und die Ergebnisse in die neue Spalte für 2017 kopieren.
HIch bekomme irgendwie ganz andere Werte..
Annahme:
in Datei2016 stehen von A1-A5 folgende Werte:
ABC
DEF
FGH
HHE
dad
in Datei2017 stehen von A1-A4 folgende Werte:
ABC
DEF
FGH
HHX
In B1 der Datei2017 trage ich dann folgendes ein und ziehe die Formel runter:
=WENNFEHLER(VERWEIS(A1;[Datei2016.xlsx]Tabelle1!$A$1:$A$17);"")Hab übrigens nicht verstanden warum ich in die 2017er Datei eine Referenzierung der 2017er Tabelle machen muss (siehe deine Formel) und warum ich von A2 anfange?
Mein Ergebnis lautet dann von B1 - B5
ABC
DEF
FGH
dad
*leer*
Das stimmt doch vorne und hinten nicht oder?
Die Formel passt so auch nicht - da ich nicht Deine echten Dateien hab, muss ich was als Beispiel basteln und beim Übertragen ist dann Quark herausgekommen.
Du kannst es auch mit dem SVERWEIS angehen (Formel in die 2017er Datei):
=sverweis(a1;[Datei2016.xlsx]Tabelle1!$A$1:$A$17;1;falsch)
Wenn A1 aus der 2017er Datei in der 2016er Datei gefunden wird, steht der Wert A1 in Spalte B. Die #NV sind die Werte, die es 2016 nicht gibt.
Die Lösung hatte ich auch schon. Mir fehlt dann aber dann die neuen Werte aus 2017.. Gibt es vielleicht eine einfachere Lösung mit VBA?
Die Werte mit NV kannst Du doch filtern und kopieren. Das ist sicherlich schneller als ein Makro zu schreiben.
Es sind zwei getrennte Dateien. Ich brauch aber als ausgabe die Werte und nicht die Anzahl der übereinstimmungen...