Compare nombres y fecha de nacimiento en dos hojas de Excel y devuelva el valor en una tercera columna

Question

Valorados programadores, espero que puedan ayudarme a modificar el código VBA a continuación para que funcione correctamente. Tengo un libro de Excel que consta de dos hojas de trabajo. La hoja de trabajo "Datos" contiene nombres (columna A), fecha de nacimiento (columna B) y números (columna C) y puede tener hasta 300.000 filas. La hoja de trabajo "Buscar" contiene nombres (columna A) y fecha de nacimiento (columna B). Quiero comparar la hoja "Buscar" con la hoja "Datos" y devolver el valor de la columna "Número" a la columna C de la pestaña "Buscar" si la fecha de nacimiento es completamente idéntico y si al menos parte del nombre es idéntico. Por ejemplo, una de las hojas puede tener solo mayúsculas o una inserción o un apellido de soltera añadido al nombre, mientras que en la otra hoja puede faltar esta información. Intenté modificar un código que encontré en línea, pero todavía soy novato en VBA y ya me da un error en el título. Parece que no puedo descubrir qué hice mal (lo coloqué en un módulo). Espero obtener algunos consejos útiles.

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

Respuesta alternativa(también porque hacerlo en fórmulas es divertido):

De hecho, intentaría evitar VBA para este. Ordenaría los "datos" por fecha de nacimiento y luego agregaría una columna auxiliar "DOB COINCIDENTES" en D2 hacia abajo, así =IF(B3=B2,D3,ROW()). Esto realizará un seguimiento de la cantidad de DOB que son iguales.

Ahora, una COINCIDENCIA en DOB devolverá el índice al primer DOB coincidente, y el valor correspondiente en la fila D indica el índice del último DOB ​​coincidente. Luego se mostrará la gama completa de nombres para aquellas fechas de nacimiento coincidentes.

=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

Ahora ingrese lo siguiente como una fórmula matricial (CTRL+MAYÚS+ENTRAR)

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

Devolverá el índice arango por encimadonde el "nombre corto" en los datos aparece en algún lugar de A2 de la hoja de BÚSQUEDA.

El número final en la columna C para el nombre y fecha de nacimiento coincidentes será

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

o todo en uno (recuerde CTRL+MAYÚS+ENTRAR):

=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)

La fórmula puede contener algunos errores, pero el concepto debería funcionar.

COMBINACIÓN MÁS FLEXIBLE Excel ofrece algunas funciones avanzadas de comparación difusa, que puede obtener de diferentes maneras según su número de versión. Si tiene un Excel 2016 o anterior, busque en Google "Complemento de búsqueda difusa para Excel". También te sugiero que veas algunos videos de entrenamiento aleatorios en youtube. Definitivamente coincidirá con, por ejemplo, JONG DE y DE JONG. Si tiene Office 365, la comparación aproximada se ha integrado en Power Query. Nuevamente echa un vistazo a youtube (por ejemplohttps://www.youtube.com/watch?v=3s5KcTNy4rs). La última opción es realmente poderosa y supongo que podrías generar los datos necesarios con unos pocos clics sin ninguna fórmula.

¿NO SE PUEDE ORDENAR?

Respuesta fácil: guarde el libro en Google Drive y ábralo/conviértalo a Google Sheets y ordénelo allí, o si es algo único, pídale a un amigo que lo haga en una PC más potente. También debe Archivo > Guardar como > Explorar > Guardar como tipo > "Libro de trabajo binario de Excel (*.xlsb). Esto podría/debería reducir los requisitos de memoria, al menos para los tiempos de guardado automático y de guardado/carga.

¡PERO MIS FÓRMULAS!

Última opción, que resuelve tanto la búsqueda ordenada como la búsqueda difusa. Puede crear su propia comparación semidifusa extrayendo, por ejemplo, las dos o más palabras más largas del campo de nombre y concatenándolas por separado con la fecha de nacimiento (por ejemplo, "01-01-1964 JONG"). Primero, crea hasta 4 columnas auxiliares y extrae cuatro palabras clave de la columna de nombre usando espacio, guión y coma como separadores. Sin ordenar, ahora puede hacer una COINCIDENCIA única basada en cada uno de los valores concatenados. El número de coincidencias le daría un nivel de confianza para emparejarlo con el número de la columna C.

Si puede ordenar (aún es preferible), solo tendrá que dividir las palabras clave en la más pequeña de las dos tablas. Y no es necesario hacer la concatenación con la DOB. Encontrará la lista de DOB como se muestra arriba y luego BUSCAR la cadena de nombre completa con cada palabra clave.