Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Excel - Spalten verschiedener Tabellen und Formate vergleichen

Frage Microsoft Microsoft Office

Mitglied: hannsgmaulwurf

hannsgmaulwurf (Level 2) - Jetzt verbinden

04.04.2012, aktualisiert 11:15 Uhr, 7391 Aufrufe, 10 Kommentare

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:

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.
Mitglied: mak-xxl
04.04.2012 um 14:11 Uhr
Moin hannsgmaulwurf,

Zitat von hannsgmaulwurf:
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
Bitte warten ..
Mitglied: hannsgmaulwurf
04.04.2012 um 14:25 Uhr
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.
Bitte warten ..
Mitglied: mak-xxl
04.04.2012 um 15:12 Uhr
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

01.
Sub TokenSearch() 
02.
 
03.
    Dim z1 As Integer, i As Integer, z2 As Integer                              ' Zählvariablen 
04.
    Dim strDelim As String                                                      ' Trenner im Datensatz 
05.
    Dim strS() As String                                                        ' Array für Datensatz 
06.
    Dim intTokS As Integer                                                      ' Soll für Grad der Übereinstimmung 
07.
    Dim intTokI As Integer                                                      ' Ist für Grad der Übereinstimmung 
08.
     
09.
    strDelim = ","                                                              ' Trenner im Datensatz 
10.
    intTokS = 2                                                                 ' Anzahl Tokens mit Übereinstimmung 
11.
     
12.
    With ThisWorkbook 
13.
        For z1 = 1 To 3                                                         ' alle Datensätze Sheet(1) in Spalte A 
14.
            strS = Split(.Sheets(1).Cells(z1, 1), strDelim) 
15.
            For z2 = 1 To 3                                                     ' alle Datensätze Sheet(2) in Spalte B 
16.
                For i = 0 To UBound(strS) 
17.
                    If InStr(1, .Sheets(2).Cells(z2, 2), strS(i), vbTextCompare) > 1 Then intTokI = intTokI + 1 
18.
                Next i 
19.
                If intTokI >= intTokS Then                                      ' Trefferauswertung 
20.
                    .Sheets(1).Cells(z1, 2) = .Sheets(1).Cells(z1, 2) & " - " & z2 
21.
                End If 
22.
                intTokI = 0 
23.
            Next z2 
24.
        Next z1 
25.
    End With 
26.
     
27.
End Sub
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
Bitte warten ..
Mitglied: hannsgmaulwurf
04.04.2012 um 15:27 Uhr
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.

Vielen Dank für deine Mühe - nach F5 passiert aber nichts bzw. was soll den für ein Ergebnis gezeigt werden?

Besten Dank.
Bitte warten ..
Mitglied: mak-xxl
04.04.2012 um 15:42 Uhr
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
Bitte warten ..
Mitglied: hannsgmaulwurf
04.04.2012 um 15:49 Uhr
Ist es denn relevant, ob die Werte in Spalte A oder in Spalte Z stehen?

Besten Dank.
Bitte warten ..
Mitglied: mak-xxl
04.04.2012 um 15:57 Uhr
Moin hannsgmaulwurf,

sehr relevant - weil in VBA 'hart codiert'. Änderbar, wenn Du weist, dass die Schreibweise
01.
Cells(z1, 1)          ' die 1 nach dem Komma meint Spalte A (z1 ist die Zeile), also ist 
02.
Cells(z1, 26)         ' die Spalte Z
ACHTUNG: Der o.a. Quelltext (Zeile 20) schreibt das Ergebnis in Spalte B auf Sheet 1:

01.
.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
Bitte warten ..
Mitglied: hannsgmaulwurf
04.04.2012 um 16:04 Uhr
Vielen Dank bis hierher mario, ich werde damit mal etwas rumexperimentieren und gebe dann nochmal feedback.

Besten Dank.
Bitte warten ..
Mitglied: mak-xxl
04.04.2012 um 16:25 Uhr
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:
01.
Dim z1 As Integer, i As Integer, z2 As Integer                        ' Zählvariablen
so:
01.
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
Bitte warten ..
Mitglied: hannsgmaulwurf
12.04.2012 um 12:32 Uhr
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!!!
Bitte warten ..
Ähnliche Inhalte
Microsoft Office
Vergleich von 2 Tabellen in Excel
gelöst Frage von abuelitoMicrosoft Office4 Kommentare

Hallo an Alle, folgendes Problem: Ich habe eine Exceldatei mit 2 Tabellen. Spalten sind identisch. Tabelle1 hat aktuelle Daten ...

Microsoft Office
Excel Tabelle Preise vergleichen
gelöst Frage von klaus1985Microsoft Office4 Kommentare

Hallo Ich hoffe mir kann einer bei diesem Problem behilflich sein . Ich bekomme jeden Tag von zwei Lieferanten ...

Microsoft Office
Excel Tabellen Vergleich
gelöst Frage von Dr.CornwallisMicrosoft Office1 Kommentar

Liebe Gemeinde, ich habe einen VBA Code, dieser vergleicht eine Spalte mit anderen Spalten aus anderen Blättern. Verglichen wird ...

VB for Applications
Excel VBA gefilterte Spalten vergleichen
gelöst Frage von YotYotVB for Applications3 Kommentare

Aloha! Ich bin seit satten fünf Stunden auf der Suche nach einer funktionierenden Lösung und scheitere daran, dass ich ...

Neue Wissensbeiträge
Humor (lol)

"gimme gimme gimme": Automatischer Test stolpert über Easter Egg im man-Tool

Information von Penny.Cilin vor 1 StundeHumor (lol)2 Kommentare

Interessant, was man so alles als Easter Egg implementiert. Ist schon wieder Ostern? "gimme gimme gimme": Automatischer Test stolpert ...

MikroTik RouterOS

Mikrotik - Lets Encrypt Zertifikate mit MetaROUTER Instanz auf dem Router erzeugen

Anleitung von colinardo vor 17 StundenMikroTik RouterOS8 Kommentare

Einleitung Folgende Anleitung ist aus der Lage heraus entstanden das ein Kunde auf seinem Mikrotik sein Hotspot Captive Portal ...

Sicherheit

Sicherheitslücke in HP-Druckern - Firmware-Updates stehen bereit

Information von BassFishFox vor 17 StundenSicherheit1 Kommentar

Ein weiterer Grund, dass Drucker keinerlei Verbindung nach "auswaerts" haben sollen. Unter Verwendung spezieller Malware können Angreifer aus der ...

Administrator.de Feedback

Entwicklertagebuch: Die Startseite wurde überarbeitet

Information von admtech vor 20 StundenAdministrator.de Feedback10 Kommentare

Hallo Administrator User, mit dem Release 5.7 haben wir unsere Startseite überarbeitet und die Beiträge und Fragen voneinander getrennt. ...

Heiß diskutierte Inhalte
Windows Server
RDP macht Server schneller???
Frage von JaniDJWindows Server17 Kommentare

Hallo Community, wir betrieben seit geraumer Zeit diverse virtuelle Maschinen und Server mit Windows Server 2012. Leider haben wir ...

Windows Netzwerk
Netzwerk Neustrukturierung
Frage von IT-DreamerWindows Netzwerk16 Kommentare

Hallo verehrte Community und Admins, bei uns im Haus steht eine Neustrukturierung an. Dafür benötige ich von euch ein ...

Windows 10
Windows 10 dunkler Bildschirm nach Umfallen
Frage von AkcentWindows 1015 Kommentare

Hallo, habe hier einen Windows 10 Rechner der von einem User umgefallen wurde (Beine übers Knie, an den PC ...

Linux
OpenSource Groupware
Frage von FA-jkaLinux13 Kommentare

Hallo, ich suche eine Groupware als Alternative zum Exchange. Wesentliche Aufgaben sind die Handhabung von E-Mails (persönliche und gemeinsam ...