Сравнить имена и дату рождения в двух таблицах Excel и вернуть значение в третий столбец

Question

Уважаемые программисты, надеюсь, вы поможете мне изменить код VBA ниже, чтобы он работал правильно. У меня есть рабочая книга Excel, состоящая из двух рабочих листов. Рабочий лист «Данные» содержит имена (столбец A), дату рождения (столбец B) и числа (столбец C) и может содержать до 300 тыс. строк. Рабочий лист «Поиск» содержит имена (столбец A) и дату рождения (столбец B). Я хочу сравнить лист «Поиск» с листом «Данные» и вернуть значение из столбца «Число» в столбец C вкладки «Поиск», если дата рождения полностью идентична и если идентична хотя бы часть имени. Например, на одном из листов могут быть только заглавные буквы или вставка или девичья фамилия, добавленная к имени, в то время как на другом листе эта информация может отсутствовать. Я попытался изменить код, который нашел в Интернете, но я все еще новичок в VBA, и он уже выдает мне ошибку в заголовке. Не могу понять, что я сделал не так (поместил в модуль). Надеюсь получить полезные советы.

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

Альтернативный ответ(еще и потому, что делать это в формулах весело):

Я бы на самом деле попытался избежать VBA для этого. Я бы отсортировал "данные" по ДР, затем добавил бы вспомогательный столбец "СООТВЕТСТВУЮЩИЕ ДР" в D2 по убыванию, как здесь =IF(B3=B2,D3,ROW()). Это будет отслеживать количество одинаковых ДР.

Теперь MATCH по DOB вернет индекс к первому совпадающему DOB, а соответствующее значение в строке D указывает индекс для последнего совпадающего DOB. Полный диапазон имен для совпадающих DOB будет тогда

=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

Теперь введите следующее как формулу массива (CTRL+SHIFT+ENTER)

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

Он вернет индекс вдиапазон вышегде «короткое имя» в данных встречается где-то в ячейке A2 листа ПОИСК.

Окончательное число в столбце C для совпадающего имени и даты рождения будет равно

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

или все в одном (помните CTRL+SHIFT+ENTER):

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

формула может содержать несколько ошибок, но концепция должна работать

БОЛЕЕ ГИБКОЕ СООТВЕТСТВИЕ Excel предлагает некоторые расширенные функции нечеткого сравнения, которые можно получить разными способами в зависимости от номера версии. Если у вас Excel 2016 или более ранняя версия, наберите в Google «Надстройка нечеткого поиска для Excel». Я также предлагаю вам посмотреть несколько случайных обучающих видео на YouTube. Он наверняка совпадет с JONG DE и DE JONG. Если у вас Office 365, нечеткое сравнение встроено в Power Query. Снова взгляните на YouTube (например,https://www.youtube.com/watch?v=3s5KcTNy4rs). Последний вариант действительно мощный, и я полагаю, что вы сможете сгенерировать необходимые данные за несколько кликов без каких-либо формул.

НЕВОЗМОЖНО СОРТИРОВАТЬ?

Простой ответ: сохраните книгу на Google Drive и откройте/конвертируйте ее в Google Sheets и отсортируйте там, или, если это разовое мероприятие, попросите друга сделать это на более мощном ПК. Вам также следует Файл > Сохранить как > Обзор > Сохранить как тип > "Excel Binary Workbook (*.xlsb)". Это может/должно снизить требования к памяти, по крайней мере для автоматического сохранения и времени сохранения/загрузки.

НО МОИ ФОРМУЛЫ!

Последний вариант, который как бы решает и сортировку, и нечеткий поиск. Вы можете построить свое собственное полунечеткое сравнение, извлекая, скажем, два или более самых длинных слова из поля имени и объединяя их отдельно с DOB (например, "01-01-1964 JONG"). Сначала вы создаете, скажем, до 4 вспомогательных столбцов и извлекаете четыре ключевых слова из столбца имени, используя пробел, тире и запятую в качестве разделителей. Без сортировки вы теперь можете сделать уникальный MATCH на основе каждого из объединенных значений. Количество совпадений даст вам уровень уверенности для сопряжения с номером столбца C.

Если вы можете сортировать (что все еще предпочтительнее), вам нужно будет только выполнить разделение ключевых слов в меньшей из двух таблиц. И не нужно делать конкатенацию с DOB. Вы находите список DOB, как указано выше, а затем выполняете ПОИСК по всей строке имени с каждым ключевым словом.