15
EXCEL - Daten aus 2 Tabellen abgleichen
Hey an die Community,
vielleicht fühlt sich ja jemand von euch angesprochen und hat eine Lösung für meine Rätselnuss:
Ausgangslage:
Es existieren 2 Excel-Tabellen mit fast dem gleichen Inhalt.
Es werden in beiden Tabellen einer ID eine IMEI zugeordnet.
Aufgabenstellung:
Es gilt nun die ID mit den IMEI's so abzugleichen, dass die Daten am Ende zusammen passen.
Eine Datenquelle (Tabelle 2) gilt als vertrauenswürdig und wird als Referenzwert genommen, die andere Tabelle (Tabelle 1) ist die Dokumentation ( da steht noch viel mehr drin) und soll deswegen abgeglichen werden. Es handelt sich um knapp 400 Zeilen, was ein händisches Abgleichen unmöglich macht.
Idee:
Wenn eine ID von Tabelle 1 mit einer ID Tabelle 2 übereinstimmt soll dann die IMEI aus Tabelle 2 eingetragen werden.
Ziel:
die Sortierung der Tablle 1 soll unverändert bleiben. Mittels Hilfstabelle möchte ich dann die passenden IMEI's den ID's zuweisen.
vielleicht fühlt sich ja jemand von euch angesprochen und hat eine Lösung für meine Rätselnuss:
Ausgangslage:
Es existieren 2 Excel-Tabellen mit fast dem gleichen Inhalt.
Es werden in beiden Tabellen einer ID eine IMEI zugeordnet.
Aufgabenstellung:
Es gilt nun die ID mit den IMEI's so abzugleichen, dass die Daten am Ende zusammen passen.
Eine Datenquelle (Tabelle 2) gilt als vertrauenswürdig und wird als Referenzwert genommen, die andere Tabelle (Tabelle 1) ist die Dokumentation ( da steht noch viel mehr drin) und soll deswegen abgeglichen werden. Es handelt sich um knapp 400 Zeilen, was ein händisches Abgleichen unmöglich macht.
Idee:
Wenn eine ID von Tabelle 1 mit einer ID Tabelle 2 übereinstimmt soll dann die IMEI aus Tabelle 2 eingetragen werden.
Ziel:
die Sortierung der Tablle 1 soll unverändert bleiben. Mittels Hilfstabelle möchte ich dann die passenden IMEI's den ID's zuweisen.
WENN [ID Tabelle 1] GLEICH [ID Tabelle 2] DANN [ID Tabelle 2] [IMEI Tabelle2]
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 7076917788
Url: https://administrator.de/contentid/7076917788
Ausgedruckt am: 21.11.2024 um 21:11 Uhr
15 Kommentare
Neuester Kommentar
Steht doch schon fast da.
Wenn-Funktion mit Sverweis.
Ja, ich weis, Index kann das besser. Ich bin aber nun mal Sverweis gewöhnt.
Wenn-Funktion mit Sverweis.
Ja, ich weis, Index kann das besser. Ich bin aber nun mal Sverweis gewöhnt.
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Zitat von @ukulele-7:
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Leider habe ich auf die Schnelle keinen SQL-Server zur Hand wo ich die 2 Tabellen einfügen kann.
Moin,
ich würde es mit Index machen:
Die Formel einfach in Tabelle 1 in die Zeile der ersten ID packen und dann sollte das passen:
=INDEX(SPALTE IMEI TABELLE 2;VERGLEICH(ID TABELLE 1;SPALTE ID TABELLE 2;0))
Das Ergebnis sollte dann immer die IMEI aus Tabelle 2 sein, sofern die Kombination vorhanden ist.
Wenn ichs richtig verstanden habe, sparst du dir dann auch die Hilfstabelle, weil du so direkt die korrekte IMEI der ID aus Tabelle 1 zuweist.
ich würde es mit Index machen:
Die Formel einfach in Tabelle 1 in die Zeile der ersten ID packen und dann sollte das passen:
=INDEX(SPALTE IMEI TABELLE 2;VERGLEICH(ID TABELLE 1;SPALTE ID TABELLE 2;0))
Das Ergebnis sollte dann immer die IMEI aus Tabelle 2 sein, sofern die Kombination vorhanden ist.
Wenn ichs richtig verstanden habe, sparst du dir dann auch die Hilfstabelle, weil du so direkt die korrekte IMEI der ID aus Tabelle 1 zuweist.
Und wo ist jetzt das Problem? F1 SVERWEIS Formel eintragen und runter ziehen, fertig.
Gruß
Gruß
Zitat von @ukulele-7:
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Vom Prozess her aber auch fragwürdig.
Wenn alle Daten bereits in Excel vorliegen, müsste man ja erst beide Tabellen in eine DB-Struktur importieren, dort vergleichen und anschließend wieder exportieren...
Ein einfacher SVERWEIS reicht ja.
In Tabelle 1, Zelle C2:
=WENNFEHLER(SVERWEIS($A2;Tabelle2!$A$2:$B$22;2;FALSCH);"")
Edit: Fehler korrigiert
Gruß
em-pie
Zitat von @kevsei:
Moin,
ich würde es mit Index machen:
Die Formel einfach in Tabelle 1 in die Zeile der ersten ID packen und dann sollte das passen:
=INDEX(SPALTE IMEI TABELLE 2;VERGLEICH(ID TABELLE 1;SPALTE ID TABELLE 2;0))
Das Ergebnis sollte dann immer die IMEI aus Tabelle 2 sein, sofern die Kombination vorhanden ist.
Wenn ichs richtig verstanden habe, sparst du dir dann auch die Hilfstabelle, weil du so direkt die korrekte IMEI der ID aus Tabelle 1 zuweist.
Moin,
ich würde es mit Index machen:
Die Formel einfach in Tabelle 1 in die Zeile der ersten ID packen und dann sollte das passen:
=INDEX(SPALTE IMEI TABELLE 2;VERGLEICH(ID TABELLE 1;SPALTE ID TABELLE 2;0))
Das Ergebnis sollte dann immer die IMEI aus Tabelle 2 sein, sofern die Kombination vorhanden ist.
Wenn ichs richtig verstanden habe, sparst du dir dann auch die Hilfstabelle, weil du so direkt die korrekte IMEI der ID aus Tabelle 1 zuweist.
Das habe ich so umgesetzt:
=INDEX(Tabelle2!B:B;VERGLEICH(A:A;Tabelle2!A:A;0))
Das Ergebnis ist hier #ÜBERLAUF!
Wähle ich stattdessen:
=INDEX(Tabelle2!B:B;VERGLEICH(A2;Tabelle2!A:A;0))
ist das Ergebnis #NV
Moin,
das untere ist korrekt.
#NV sagt aus, dass der Vergleich nicht übereinstimmt.
Das heißt A2 gibt es nicht in Tabelle 2 in Spalte A. Du musst hier aber beachten, dass auch die Zahlenformate und Formatierungen usw. identisch sind.
das untere ist korrekt.
#NV sagt aus, dass der Vergleich nicht übereinstimmt.
Das heißt A2 gibt es nicht in Tabelle 2 in Spalte A. Du musst hier aber beachten, dass auch die Zahlenformate und Formatierungen usw. identisch sind.
Zitat von @em-pie:
Vom Prozess her aber auch fragwürdig.
Wenn alle Daten bereits in Excel vorliegen, müsste man ja erst beide Tabellen in eine DB-Struktur importieren, dort vergleichen und anschließend wieder exportieren...
Je nach DBMS gibt es häufig gute Import-Assistenten. Bei MSSQL käme sogar OPENROWSET in Betracht und damit einfach mal beide Tabellen mit EXCEPT vergleichen. Ist dann halt quick and dirty aber robuster als Excel Formeln.Zitat von @ukulele-7:
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Geht natürlich mit Excel und SVERWEIS aber optimal wäre das mit SQL abzugleichen.
Vom Prozess her aber auch fragwürdig.
Wenn alle Daten bereits in Excel vorliegen, müsste man ja erst beide Tabellen in eine DB-Struktur importieren, dort vergleichen und anschließend wieder exportieren...
Soll das ganze den öfter abgeglichen werden oder einmalig? Wäre noch interessant zu wissen.
Zitat von @kevsei:
Moin,
das untere ist korrekt.
#NV sagt aus, dass der Vergleich nicht übereinstimmt.
Das heißt A2 gibt es nicht in Tabelle 2 in Spalte A. Du musst hier aber beachten, dass auch die Zahlenformate und Formatierungen usw. identisch sind.
Moin,
das untere ist korrekt.
#NV sagt aus, dass der Vergleich nicht übereinstimmt.
Das heißt A2 gibt es nicht in Tabelle 2 in Spalte A. Du musst hier aber beachten, dass auch die Zahlenformate und Formatierungen usw. identisch sind.
Ich hab das jetzt mal 1:1 mit deinen Zellbbezügen nachempfunden und das ergebnis bleibt #NV.
Wie im Screenshot zu sehen ist, wären vergleichbare Werte vorhanden.
Wo liegt hier mein Fehler?
Wo liegt hier mein Fehler?
Ich tippe auf versteckte Leerzeichen in den Daten...p.s. gibt es einen guten Grund warum Index() mit Vergleich() benutzt wird ? SVERWEIS macht ja im Endeffekt das Gleiche ... s. Beispiele oben
p.s.2 Ganze Spaltenverweise ala A:A etc. sind ineffektiv und kosten unnötig Rechenleistung.
Hey, zieh die Formel mal runter. Wenn überall #NV steht ist ggf. z.B. in Spalte C eine Zahl und in Spalte F ein Text. Dann funktioniert der Vergleich nicht. Das müsstest du dann in der Formel ggf. anpassen oder die Werte entsprechend formatieren. Das siehst du am einfachsten, wenn du eine Zelle in C anklickst und oben bei Formatierung schaust.
Am einfachsten mit Hilfsspalte und folgendes einfügen:
Wobei dann in E2 folgende Formel ist:
=ZAHLENWERT(F2)
Die Formel dann runterziehen.
Dann in D2 folgendes:
=INDEX(G:G;VERGLEICH(ZAHLENWERT(C2);E:E;0))
und runterziehen.
Am einfachsten mit Hilfsspalte und folgendes einfügen:
Wobei dann in E2 folgende Formel ist:
=ZAHLENWERT(F2)
Die Formel dann runterziehen.
Dann in D2 folgendes:
=INDEX(G:G;VERGLEICH(ZAHLENWERT(C2);E:E;0))
und runterziehen.
Hey,
Ja SVWERWEIS macht das gleiche, ist aber etwas unflexibler, wenn z.B. Spalten hinzugefügt werden oder etwas kopiert wird, da der Spaltenindex festgesetzt ist. Das macht Index deutlich flexibler und ist Performancetechnisch deutlich schneller (bei Datensätzen deutlich größer als dem hier).
Edit1: Man kann natürlich die Grenzen der Matrix festlegen, das Problem ist dann aber, sollten Daten hinzugefügt werden und man vergisst das Anpassen der Formel, fehlt halt der Teil. Bei den Mengen um die es hier geht, ist die Spalten Angabe performancetechnisch meiner Meinung nach uninteressant. Idealerweise arbeitet man bei großen Datensätzen dann mit Bereich.Verschieben.
Ich mag es, wenn man mit Excel arbeitet, dass die Formeln flexibel bleiben und nicht bei jeder Änderung am Datensatz angepasst werden müssen.
Ist das jetz hier eine einmalige Geschichte, hat Ultramatic natürlich recht und man kann auch SVERWEIS nutzen und auch die Zellen eingrenzen.
Ja SVWERWEIS macht das gleiche, ist aber etwas unflexibler, wenn z.B. Spalten hinzugefügt werden oder etwas kopiert wird, da der Spaltenindex festgesetzt ist. Das macht Index deutlich flexibler und ist Performancetechnisch deutlich schneller (bei Datensätzen deutlich größer als dem hier).
Edit1: Man kann natürlich die Grenzen der Matrix festlegen, das Problem ist dann aber, sollten Daten hinzugefügt werden und man vergisst das Anpassen der Formel, fehlt halt der Teil. Bei den Mengen um die es hier geht, ist die Spalten Angabe performancetechnisch meiner Meinung nach uninteressant. Idealerweise arbeitet man bei großen Datensätzen dann mit Bereich.Verschieben.
Ich mag es, wenn man mit Excel arbeitet, dass die Formeln flexibel bleiben und nicht bei jeder Änderung am Datensatz angepasst werden müssen.
Ist das jetz hier eine einmalige Geschichte, hat Ultramatic natürlich recht und man kann auch SVERWEIS nutzen und auch die Zellen eingrenzen.
Ich sehe da schon ein Problem.
Du hast bei deinem Beispiel eine ID mit führender Null. Da das Excel ist, hast du da zwangsweise mindestens an den Formaten rumgespielt.
Übrigens kannst du dir den kompletten Vergleich im Grunde sparen. Du brauchst nur die IMEI zu ID2 suchen.
Weil es soll ja nur die IMEI drinstehen, wenn ID1 dasselbe wie ID2 ist.
Ansonsten empfehle ich dir dass du die beiden Spalten mit den IDs in Zahlen konvertiertst. Das dürfte am einfachsten gehen, wenn du die Inhalte kopierst und die dann über Inhalte Einfügen per Addition "in Zahlen umwandelst"
Du hast bei deinem Beispiel eine ID mit führender Null. Da das Excel ist, hast du da zwangsweise mindestens an den Formaten rumgespielt.
Übrigens kannst du dir den kompletten Vergleich im Grunde sparen. Du brauchst nur die IMEI zu ID2 suchen.
Weil es soll ja nur die IMEI drinstehen, wenn ID1 dasselbe wie ID2 ist.
Ansonsten empfehle ich dir dass du die beiden Spalten mit den IDs in Zahlen konvertiertst. Das dürfte am einfachsten gehen, wenn du die Inhalte kopierst und die dann über Inhalte Einfügen per Addition "in Zahlen umwandelst"
Vielen Dank, nachdem ich die Werte hin und her kopiert hatte, und dann letztendlich mittels [STRG] + [ALT] + [V] die Essenz der Daten übernehmen konnte, hat dann auch die INDEX Formel funktioniert.
Somit konnte ich meine Zielstellung erreichen und der Abgleich der Daten ist nun abgeschlossen.
Vielen Dank an Alle, die sich hier so rege beteiligt haben.
Somit konnte ich meine Zielstellung erreichen und der Abgleich der Daten ist nun abgeschlossen.
Vielen Dank an Alle, die sich hier so rege beteiligt haben.
