Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

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 1) - Jetzt verbinden

04.04.2012, aktualisiert 11:15 Uhr, 6995 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 ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Microsoft Office
Excel 2010 - Zwei Tabellen vergleichen (Zugriffsprüfung) (2)

Frage von d4shoerncheN zum Thema Microsoft Office ...

Microsoft Office
gelöst Excel Spalten (6)

Frage von a.grothe zum Thema Microsoft Office ...

Microsoft Office
Excel 2016 anzeige der markierten Spalten (1)

Frage von endurance zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

Outlook & Mail
gelöst Outlook 2010 findet ost datei nicht (19)

Frage von Floh21 zum Thema Outlook & Mail ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Windows Server
Server 2008R2 startet nicht mehr (Bad Patch 0xa) (18)

Frage von Haures zum Thema Windows Server ...