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

Mitglied: hannsgmaulwurf

hannsgmaulwurf (Level 2) - Jetzt verbinden

04.04.2012, aktualisiert 11:15 Uhr, 7502 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
Windows 10

USB Maus und Tastatur versagen Dienst unter Windows 10

Erfahrungsbericht von hardykopff vor 1 TagWindows 105 Kommentare

Da steht man ziemlich dumm da, wenn der PC sich wegen fehlender USB Tastatur und Maus nicht bedienen lässt. ...

Administrator.de Feedback
Update der Seite: Alles zentriert
Information von Frank vor 1 TagAdministrator.de Feedback18 Kommentare

Hallo User, die größte Änderung von Release 5.8 ist das Zentrieren der Webseite (auf großen Bildschirmen) und ein "Welcome"-Teaser ...

Humor (lol)

WhatsApp-Nachrichten endlich auch per Bluetooth versendbar

Information von BassFishFox vor 2 TagenHumor (lol)4 Kommentare

Genau darauf habe ich gewartet! ;-) Der beliebte Messaging-Dienst WhatsApp erhält eine praktische neue Funktion: Ab dem nächsten Update ...

Google Android

Googles "Android Enterprise Recommended" für Unternehmen

Information von kgborn vor 3 TagenGoogle Android3 Kommentare

Hier eine Information, die für Administratoren und Verantwortliche in Unternehmen, die für die Beschaffung und das Rollout von Android-Geräten ...

Heiß diskutierte Inhalte
Speicherkarten
Vergessliche USB-Sticks?
Frage von hanheikSpeicherkarten14 Kommentare

Ich habe in den letzten Tagen 500 USB-Sticks mit Bilddateien bespielt. Obwohl ich die Dateien mit größter Sorgfalt kopiert ...

Windows Server
NTFS Berechtigungen Ordnerstruktur
Frage von hukahu23489Windows Server11 Kommentare

Hallo, ich bin seit kurzem in einer neuen IT-Abteilung und bin über das Berechtigungskonzept des Unternehmens sehr schockiert. Ich ...

Exchange Server
NDR umleiten
Frage von isomasterExchange Server10 Kommentare

Hallo Kollege, ich habe ein Problem mit dem Exchange Server. Wir haben unsere info@ Adresse als Verteilergruppe eingebunden (so ...

Windows Server
Standortvernetzung zu einem Strato VServer (Windows)
Frage von matzefratze81Windows Server10 Kommentare

Moin, ich komme aus einem Enterprise-Umfeld und habe den Fehler gemacht, dass ich mich auf ein kleines Unternehmen eingelassen ...