10
Excel - Spalten verschiedener Tabellen und Formate vergleichen
Es sollen Spalten zweier unterschiedlicher Tabellen mit unterschiedlichen Formaten auf gleiche oder ansatzweise bzw. teilweise gleiche Inhalte verglichen werden.
Hallo zusammen,
Gegeben sind die Excel Dateien Tabelle1.xls und Tabelle2.xls. In beiden befinden sich Adresseinträge in unterschieldichen Formaten. Nun sollen beide Tabellen verglichen werden, ob gewisse Zeichenfolgen der einen Tabelle in der zweiten Tabelle vorkommen. Mir ist bisher nichts passendes auf Formel-Ebene eingefallen, da die Abfrage ja recht umfangreich wäre. Ich hätte z.B. in die Richtung "Nimm dir die ersten fünf Zeichen der einen Zelle und vergleiche sie mit allen zellen der anderen Tabelle." Aber diese Überlegung habe ich schnell überworfen, da beispielsweise schon ein einziges Leerzeichen mehr keinen Treffer bringen würde (wie im unteren Beispiel bei Mittelweg_5).
Hier mal zur Verdeutlichung:
Tabelle1:
Tabelle2:
Hat vielleicht jemand eine Idee, wie man das lösen könnte? Habe auch schon daran gedacht, die Excel Dateien als csv oder Ähnliches zu exportieren und mit notepad++ zu vergleichen - seeeeehhhrrrr unübersichtlich
Vielen Dank vorab.
Gegeben sind die Excel Dateien Tabelle1.xls und Tabelle2.xls. In beiden befinden sich Adresseinträge in unterschieldichen Formaten. Nun sollen beide Tabellen verglichen werden, ob gewisse Zeichenfolgen der einen Tabelle in der zweiten Tabelle vorkommen. Mir ist bisher nichts passendes auf Formel-Ebene eingefallen, da die Abfrage ja recht umfangreich wäre. Ich hätte z.B. in die Richtung "Nimm dir die ersten fünf Zeichen der einen Zelle und vergleiche sie mit allen zellen der anderen Tabelle." Aber diese Überlegung habe ich schnell überworfen, da beispielsweise schon ein einziges Leerzeichen mehr keinen Treffer bringen würde (wie im unteren Beispiel bei Mittelweg_5).
Hier mal zur Verdeutlichung:
Tabelle1:
| Spalte A | Spalte B | |
|---|---|---|
| Müller, Klaus, Mittelweg 5, Hamburg | Ist diese Adresse in Tabelle2 vorhanden? Wenn ja, gib den Wert aus Spalte A der entsprechenden Zeile von Tabelle2 aus. | |
| Schulze, Erika, Parkstraße 1, Hamburg | Ist diese Adresse in Tabelle2 vorhanden? Wenn ja, gib den Wert aus Spalte A der entsprechenden Zeile von Tabelle2 aus. | |
| Hans Schmitt im Mittelweg 5, Hamburg | Ist diese Adresse in Tabelle2 vorhanden? Wenn ja, gib den Wert aus Spalte A der entsprechenden Zeile von Tabelle2 aus. |
Tabelle2:
| Spalte A | Spalte B | |
|---|---|---|
| 1. | Franz Kafka, Sonnenallee 7, Bremen | |
| 2. | H. Schmitt, Hamburg, Mittelweg5 | |
| 3. | Parkstraße 1, 20095 Hamburg, Frau Erika Schulze |
Hat vielleicht jemand eine Idee, wie man das lösen könnte? Habe auch schon daran gedacht, die Excel Dateien als csv oder Ähnliches zu exportieren und mit notepad++ zu vergleichen - seeeeehhhrrrr unübersichtlich
Vielen Dank vorab.
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 183061
Url: https://administrator.de/contentid/183061
Printed on: April 24, 2024 at 19:04 o'clock
10 Comments
Latest comment
Moin hannsgmaulwurf,
Bist Du denn auf eine Formellösung angewiesen - oder darf es auch per VBA sein?
Freundliche Grüße von der Insel - Mario
Zitat von @hannsgmaulwurf:
Mir ist bisher nichts passendes auf Formel-Ebene eingefallen, da die Abfrage ja recht umfangreich wäre.
Mir ist bisher nichts passendes auf Formel-Ebene eingefallen, da die Abfrage ja recht umfangreich wäre.
Bist Du denn auf eine Formellösung angewiesen - oder darf es auch per VBA sein?
Freundliche Grüße von der Insel - Mario
Hallo Mario,
eine Formel wäre mir lieber, da ich mich mit VBA überhaupt nicht auskenne und das ganze doch gerne nachvollziehen möchte. Aber natürlich ist auch VBA ok
Danke vorab.
eine Formel wäre mir lieber, da ich mich mit VBA überhaupt nicht auskenne und das ganze doch gerne nachvollziehen möchte. Aber natürlich ist auch VBA ok
Danke vorab.
Moin hannsgmaulwurf,
folgenden Code kopierst Du ('Quelltext') und fügst ihn wie folgt in Deine <Tabelle1.xls> ein:
- <Tabelle1.xls> öffnen
- ALT+F11
- links 'Tabelle1 (Tabelle1)' doppelt anklicken
- rechts auf das weiße Blatt einfügen
- <Tabelle1.xls> speichern
Jetzt fügst Du die Tabelle mit den zu vergleichenden Daten (<Tabelle2.xls>) als 2. Tabellenblatt in <Tabelle1.xls> ein, speicherst <Tabelle1.xls> und schließt <Tabelle2.xls>.
Nach Erledigung dieser Dinge gehst Du wieder in den VBA-Editor (dort, wo der o.a. Quelltext steht), platzierst den Cursor im Quelltext, drückst F5 und meldest das Ergebnis.
Erläuterungen zur Funktion gebe ich in einem extra Post.
Freundliche Grüße von der Insel - Mario
folgenden Code kopierst Du ('Quelltext') und fügst ihn wie folgt in Deine <Tabelle1.xls> ein:
- <Tabelle1.xls> öffnen
- ALT+F11
- links 'Tabelle1 (Tabelle1)' doppelt anklicken
- rechts auf das weiße Blatt einfügen
- <Tabelle1.xls> speichern
Sub TokenSearch()
Dim z1 As Integer, i As Integer, z2 As Integer ' Zählvariablen
Dim strDelim As String ' Trenner im Datensatz
Dim strS() As String ' Array für Datensatz
Dim intTokS As Integer ' Soll für Grad der Übereinstimmung
Dim intTokI As Integer ' Ist für Grad der Übereinstimmung
strDelim = "," ' Trenner im Datensatz
intTokS = 2 ' Anzahl Tokens mit Übereinstimmung
With ThisWorkbook
For z1 = 1 To 3 ' alle Datensätze Sheet(1) in Spalte A
strS = Split(.Sheets(1).Cells(z1, 1), strDelim)
For z2 = 1 To 3 ' alle Datensätze Sheet(2) in Spalte B
For i = 0 To UBound(strS)
If InStr(1, .Sheets(2).Cells(z2, 2), strS(i), vbTextCompare) > 1 Then intTokI = intTokI + 1
Next i
If intTokI >= intTokS Then ' Trefferauswertung
.Sheets(1).Cells(z1, 2) = .Sheets(1).Cells(z1, 2) & " - " & z2
End If
intTokI = 0
Next z2
Next z1
End With
End SubJetzt fügst Du die Tabelle mit den zu vergleichenden Daten (<Tabelle2.xls>) als 2. Tabellenblatt in <Tabelle1.xls> ein, speicherst <Tabelle1.xls> und schließt <Tabelle2.xls>.
Nach Erledigung dieser Dinge gehst Du wieder in den VBA-Editor (dort, wo der o.a. Quelltext steht), platzierst den Cursor im Quelltext, drückst F5 und meldest das Ergebnis.
Erläuterungen zur Funktion gebe ich in einem extra Post.
Freundliche Grüße von der Insel - Mario
Zitat von @mak-xxl:
Nach Erledigung dieser Dinge gehst Du wieder in den VBA-Editor (dort, wo der o.a. Quelltext steht), platzierst den Cursor im
Quelltext, drückst F5 und meldest das Ergebnis.
Nach Erledigung dieser Dinge gehst Du wieder in den VBA-Editor (dort, wo der o.a. Quelltext steht), platzierst den Cursor im
Quelltext, drückst F5 und meldest das Ergebnis.
Vielen Dank für deine Mühe - nach F5 passiert aber nichts bzw. was soll den für ein Ergebnis gezeigt werden?
Besten Dank.
Moin hannsgmaulwurf,
nun, wenn Deine Tabellen bei jeweils den ersten 3 Einträge so aufgebaut ist wie Du das anfangs gepostet hast, sollte im ersten Sheet in Spalte B ein Verweis auf einen in etwa adäquaten Eintrag in Sheet 2 zu finden sein.
Wenn nicht, so muss im Quelltext angegeben werden, dass alle Zeilen mit Einträgen durchlaufen werden. Dazu musst Du in Zeile 13 statt der 3 die Zahl der letztbelegten Zeile in Sheet 1 und in Zeile 15 statt der 3 die Zahl der letztbelegten Zeile in Sheet 2 notieren - und F5 und Sheet 1 anschauen und Mitteilung.
Freundliche Grüße von der Insel - Mario
nun, wenn Deine Tabellen bei jeweils den ersten 3 Einträge so aufgebaut ist wie Du das anfangs gepostet hast, sollte im ersten Sheet in Spalte B ein Verweis auf einen in etwa adäquaten Eintrag in Sheet 2 zu finden sein.
Wenn nicht, so muss im Quelltext angegeben werden, dass alle Zeilen mit Einträgen durchlaufen werden. Dazu musst Du in Zeile 13 statt der 3 die Zahl der letztbelegten Zeile in Sheet 1 und in Zeile 15 statt der 3 die Zahl der letztbelegten Zeile in Sheet 2 notieren - und F5 und Sheet 1 anschauen und Mitteilung.
Freundliche Grüße von der Insel - Mario
Ist es denn relevant, ob die Werte in Spalte A oder in Spalte Z stehen?
Besten Dank.
Besten Dank.
Moin hannsgmaulwurf,
sehr relevant - weil in VBA 'hart codiert'. Änderbar, wenn Du weist, dass die Schreibweise
ACHTUNG: Der o.a. Quelltext (Zeile 20) schreibt das Ergebnis in Spalte B auf Sheet 1:
Das muss angepasst werden, wenn das anders ist als von Dir in der Anfrage beschrieben!
Freundliche Grüße von der Insel - Mario
sehr relevant - weil in VBA 'hart codiert'. Änderbar, wenn Du weist, dass die Schreibweise
Cells(z1, 1) ' die 1 nach dem Komma meint Spalte A (z1 ist die Zeile), also ist
Cells(z1, 26) ' die Spalte Z ACHTUNG: Der o.a. Quelltext (Zeile 20) schreibt das Ergebnis in Spalte B auf Sheet 1:
.Sheets(1).Cells(z1, 2) = .Sheets(1).Cells(z1, 2) & " - " & z2 Das muss angepasst werden, wenn das anders ist als von Dir in der Anfrage beschrieben!
Freundliche Grüße von der Insel - Mario
Vielen Dank bis hierher mario, ich werde damit mal etwas rumexperimentieren und gebe dann nochmal feedback.
Besten Dank.
Besten Dank.
Moin hannsgmaulwurf,
dann hier noch eine kurze Beschreibung, was der VBA-Code so treiben soll:
Zeile 3-5 sind Deklarationen von Variablen. Wenn Du z.B. mehr als ca. 32.000 Adressen hast, so muss folgendes für Zeile 3 geschrieben werden:
statt:
so:
In Zeile 9 legst Du fest, mit welchem Zeichen die Wörter des Adressdatensatzes auf Sheet 1 getrennt sind - zwischen die Literale kommt also ein Komma, Semikolon, Leerzeichen etc.
In Zeile 10 steht die Anzahl von Übereinstimmungen, die bei einem Datensatz gefunden werden müssen, also so.
- Datensatz 1 (Sheet 1) wird zerlegt in: Müller und Klaus und Mittelweg 5 und Hamburg.
- ist die Zahl z.B. 3, so müssen 3 der 4 Wörter in einem Datensatz auf Sheet 2 gefunden werden.
In Zeile 13 beginnt eine Zählschleife über 3 (alle) Zeilen des Sheet 1, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 14 wird der durch z1 angegebene Datensatz zerlegt (an der Kommastelle) und in ein Array geschrieben (strS)
In Zeile 15 beginnt eine Zählschleife über 3 (alle) Zeilen des Sheet 2, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 16 beginnt eine Zählschleife über alle Wörter des Arrays aus Zeile 14, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 17 wird geprüft, ob das Einzelwort an irgendeiner Stelle im Datensatz des Sheets 2 gefunden wird, wenn ja, wird ein Zähler inkrementiert. Das wird für alle Wörter im Array erledigt.
In Zeile 18 wird geprüft, ob gleichviel oder mehr Treffer (Ist) als in Zeile 10 festgelegt (Soll) gefunden wurden, wenn ja, wird die Zeile (z2) im Sheet 1, Spalte B als Fundzeile angegeben.
Die Befehle 'Next ...' halten die Schleifen in Gang.
Freundliche Grüße von der Insel - Mario
dann hier noch eine kurze Beschreibung, was der VBA-Code so treiben soll:
Zeile 3-5 sind Deklarationen von Variablen. Wenn Du z.B. mehr als ca. 32.000 Adressen hast, so muss folgendes für Zeile 3 geschrieben werden:
statt:
Dim z1 As Integer, i As Integer, z2 As Integer ' Zählvariablen Dim z1 As Long, i As Integer, z2 As Long ' Zählvariablen In Zeile 9 legst Du fest, mit welchem Zeichen die Wörter des Adressdatensatzes auf Sheet 1 getrennt sind - zwischen die Literale kommt also ein Komma, Semikolon, Leerzeichen etc.
In Zeile 10 steht die Anzahl von Übereinstimmungen, die bei einem Datensatz gefunden werden müssen, also so.
- Datensatz 1 (Sheet 1) wird zerlegt in: Müller und Klaus und Mittelweg 5 und Hamburg.
- ist die Zahl z.B. 3, so müssen 3 der 4 Wörter in einem Datensatz auf Sheet 2 gefunden werden.
In Zeile 13 beginnt eine Zählschleife über 3 (alle) Zeilen des Sheet 1, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 14 wird der durch z1 angegebene Datensatz zerlegt (an der Kommastelle) und in ein Array geschrieben (strS)
In Zeile 15 beginnt eine Zählschleife über 3 (alle) Zeilen des Sheet 2, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 16 beginnt eine Zählschleife über alle Wörter des Arrays aus Zeile 14, pro Zeile werden die inliegenden Befehle ausgeführt.
In Zeile 17 wird geprüft, ob das Einzelwort an irgendeiner Stelle im Datensatz des Sheets 2 gefunden wird, wenn ja, wird ein Zähler inkrementiert. Das wird für alle Wörter im Array erledigt.
In Zeile 18 wird geprüft, ob gleichviel oder mehr Treffer (Ist) als in Zeile 10 festgelegt (Soll) gefunden wurden, wenn ja, wird die Zeile (z2) im Sheet 1, Spalte B als Fundzeile angegeben.
Die Befehle 'Next ...' halten die Schleifen in Gang.
Freundliche Grüße von der Insel - Mario
Hallo,
wollte nur kurz Bescheid geben, dass ich das Problem ohne VBA gelöst habe, indem ich - zugegeben mit etwas Mehraufwand - die Adressspalten in einzelne Zellen aufgeteilt habe (mittels Trennzeichen) und diese dann alle miteinander verglichen.
Vielen Dank aber nochmals für deine Hilfe Mario!!!
wollte nur kurz Bescheid geben, dass ich das Problem ohne VBA gelöst habe, indem ich - zugegeben mit etwas Mehraufwand - die Adressspalten in einzelne Zellen aufgeteilt habe (mittels Trennzeichen) und diese dann alle miteinander verglichen.
Vielen Dank aber nochmals für deine Hilfe Mario!!!
