Vergleichen Sie Namen und Geburtsdatum in zwei Excel-Tabellen und geben Sie den Wert in einer dritten Spalte zurück.

Question

Geschätzte Programmierer, ich hoffe, Sie können mir helfen, den folgenden VBA-Code so zu ändern, dass er richtig funktioniert. Ich habe eine Excel-Arbeitsmappe, die aus zwei Arbeitsblättern besteht. Das Arbeitsblatt „Daten“ enthält Namen (Spalte A), Geburtsdatum (Spalte B) und Zahlen (Spalte C) und kann bis zu 300.000 Zeilen umfassen. Das Arbeitsblatt „Suchen“ enthält Namen (Spalte A) und Geburtsdatum (Spalte B). Ich möchte das Arbeitsblatt „Suchen“ mit dem Arbeitsblatt „Daten“ vergleichen und den Wert aus der Spalte „Zahl“ in Spalte C der Registerkarte „Suchen“ zurückgeben, wenn das Geburtsdatum vollständig identisch ist und wenn mindestens ein Teil des Namens identisch ist. Eines der Arbeitsblätter kann beispielsweise nur Großbuchstaben oder eine Einfügung oder einen Mädchennamen enthalten, während diese Information im anderen Arbeitsblatt fehlen könnte. Ich habe versucht, einen Code zu ändern, den ich online gefunden habe, bin aber noch ein Neuling bei VBA und es gibt mir bereits beim Titel einen Fehler. Ich kann nicht herausfinden, was ich falsch gemacht habe (habe es in ein Modul eingefügt). Hoffe, einige nützliche Tipps zu bekommen.

Option Explicit

    Sub Search()

        Dim i As Long, j As Long
        Dim LastrowS1 As Long, LastrowS2 As Long
        Dim NameS1 As String, DOBS1 As String, NameS2 As String, DOBS2 As String
        Dim NumberS1 As Number

        LastrowS1 = Data.Cells(Data.Rows.Count, "A").End(xlUp).Row
        LastrowS2 = Search.Cells(Search.Rows.Count, "A").End(xlUp).Row

        For i = 2 To LastrowS1
            With ThisWorkbook.Worksheets("Data")
                NameS1 = .Range("A" & i).Value
                DOBS1 = .Range("B" & i).Value
                NumberS1 = .Range("C" & i).Value
            End With
                For j = 2 To LastrowS2
                    With ThisWorkbook.Worksheets("Search")
                        NameS2 = .Range("A" & j).Value
                        DOBS2 = .Range("B" & j).Value
                    End With

                    If NameS1 = NameS2 And DOBS1 = DOBS2 Then
                        Search.Range("C" & j).Value = NumberS1
                        Exit For
                    End If

                Next j

            Next i

    End Sub

Answer 1

Alternative Antwort(auch weil es Spaß macht, es in Formeln umzusetzen):

Ich würde in diesem Fall tatsächlich versuchen, VBA zu vermeiden. Ich würde „Daten“ nach Geburtsdatum sortieren und dann in D2 eine Hilfsspalte „ÜBEREINSTIMMENDE GEBURTSDATUM“ nach unten hinzufügen, etwa so =IF(B3=B2,D3,ROW()). Dadurch wird die Anzahl identischer Geburtsdaten nachverfolgt.

Jetzt gibt ein MATCH auf DOB den Index zum ersten übereinstimmenden DOB zurück, und der entsprechende Wert in Zeile D gibt den Index für das letzte übereinstimmende DOB an. Der vollständige Namensbereich für diese übereinstimmenden DOBs wird dann

=INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0)))               ...can be optimised a bit with LET in office 365 to only calculate MATCH(...) once

Geben Sie nun Folgendes als Matrixformel ein (STRG+UMSCHALT+EINGABE)

=MATCH(TRUE, ISNUMBER(SEARCH(*range above*,A2)), 0)           ...array formula CTRL+SHIFT+ENTER

Der Index wird zurückgegeben inBereich überwobei der „Kurzname“ in den Daten irgendwo in A2 des Suchblatts vorkommt.

Die letzte Zahl in Spalte C für den passenden Namen und das Geburtsdatum lautet

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + *match index above* - 1)

oder All-in-One (denken Sie an STRG+UMSCHALT+EINGABE):

=INDEX(Data!$C:$C, MATCH(B2,Data!$B:$B,0) + MATCH(TRUE, ISNUMBER(SEARCH(INDEX(Data!$A:$A,MATCH(B2,Data!$B:$B,0)):INDEX(Data!$A:$A,INDEX(Data!$D:$D,MATCH(B2,Data!$B:$B,0))),A2)), 0) - 1)

Die Formel kann einige Fehler enthalten, aber das Konzept sollte funktionieren

FLEXIBLERES MATCHING Excel bietet einige erweiterte Fuzzy-Vergleichsfunktionen, die Sie je nach Versionsnummer auf unterschiedliche Weise erhalten können. Wenn Sie Excel 2016 oder älter haben, googeln Sie „Fuzzy Lookup Add-In für Excel“. Ich schlage auch vor, dass Sie sich ein paar zufällige Schulungsvideos dazu auf YouTube ansehen. Es wird mit Sicherheit JONG DE und DE JONG abgleichen. Wenn Sie Office 365 haben, ist Fuzzy-Vergleich in Power Query integriert. Schauen Sie sich auch hier YouTube an (z. B.https://www.youtube.com/watch?v=3s5KcTNy4rs). Die letztere Option ist wirklich leistungsstark und ich schätze, dass Sie die benötigten Daten mit wenigen Klicks und ohne Formeln generieren können.

SORTIEREN NICHT MÖGLICH?

Einfache Antwort: Speichern Sie die Arbeitsmappe auf Google Drive und öffnen/konvertieren Sie sie in Google Sheets und sortieren Sie dort, oder bitten Sie einen Freund, dies auf einem leistungsstärkeren PC zu tun, wenn es sich nur um eine einmalige Sache handelt. Sie sollten auch Datei > Speichern unter > Durchsuchen > Dateityp > „Excel-Binärarbeitsmappe (*.xlsb)“ verwenden. Dies könnte/sollte den Speicherbedarf verringern, zumindest für die automatische Speicherung und die Speicher-/Ladezeiten.

ABER MEINE FORMELEN!

Letzte Option, die sowohl die Sortierung als auch die Fuzzy-Suche irgendwie löst. Sie können Ihren eigenen halbwegs unscharfen Vergleich erstellen, indem Sie beispielsweise die zwei oder mehr längsten Wörter aus dem Namensfeld extrahieren und diese separat mit dem Geburtsdatum verknüpfen (z. B. „01-01-1964 JONG“). Zuerst erstellen Sie beispielsweise bis zu 4 Hilfsspalten und extrahieren vier Schlüsselwörter aus der Namensspalte, wobei Sie Leerzeichen, Bindestrich und Komma als Trennzeichen verwenden. Ohne zu sortieren könnten Sie nun einen eindeutigen MATCH basierend auf jedem der verknüpften Werte durchführen. Die Anzahl der Übereinstimmungen würde Ihnen ein Konfidenzniveau geben, das Sie mit der Zahl in Spalte C verknüpfen können.

Wenn Sie sortieren können (was immer noch vorzuziehen ist), müssen Sie die Schlüsselwortaufteilung nur in der kleineren der beiden Tabellen durchführen. Und die Verkettung mit dem Geburtsdatum ist nicht erforderlich. Sie finden die Liste der Geburtsdaten wie oben beschrieben und DURCHSUCHEN dann die gesamte Namenszeichenfolge mit jedem Schlüsselwort.