У Фарриса есть рабочий лист с адресами. Некоторые адреса очень близки к одному и тому же, например, почтовый адрес такой же, а отличается только часть адреса. Например, одна строка может иметь адрес «85 Seymour Street, Suite 101», а другая строка может иметь адрес «85 Seymour Street, Suite 412». Фаррис задается вопросом, как удалить дубликаты в списке адресов на основе частичного совпадения, основанного только на почтовом адресе и игнорировании номера набора.

Самое простое решение — разделить адреса на отдельные столбцы, чтобы номер набора был в отдельном столбце. Вы можете сделать это, выполнив следующие действия:

  1. Убедитесь, что справа от столбца адреса есть пустой столбец.

  2. Выделите ячейки, содержащие адреса.

  3. Выберите «Текст в столбцы» в меню «Данные». Excel запускает мастер преобразования текста в столбцы. (См. Рис. 1.)

  4. На первом этапе мастера убедитесь, что выбран параметр «С разделителями», затем нажмите «Далее».

  5. На втором этапе мастера убедитесь, что установлен флажок «Запятая», затем нажмите «Далее».

  6. На третьем шаге мастера нажмите Готово.

Уличный адрес теперь должен находиться в исходном столбце, а ранее пустой столбец должен содержать все, что было после запятой в исходных адресах. Другими словами, номер набора находится в отдельной колонке. Когда ваши данные находятся в этом состоянии, можно легко использовать фильтрацию для отображения или извлечения уникальных уличных адресов.

Если вы не хотите постоянно разделять адреса на два столбца, вы можете использовать формулу для определения дубликатов. Предполагая, что список адресов отсортирован, вы можете использовать формулу, подобную следующей:

=IF(OR(ISERROR(FIND(",",A3)),ISERROR(FIND(",",A2))), "",IF(LEFT(A3,FIND(",",A3))=LEFT(A2,FIND(",",A2)), "Duplicate",""))

Эта формула предполагает, что проверяемые адреса находятся в столбце A, а эта формула размещена где-то в строке 3 другого столбца. Сначала он проверяет, есть ли запятая в адресе в текущей строке или в адресе в предыдущей строке. Если в любом из адресов нет запятой, предполагается, что дубликата нет.

Если в них обоих есть запятая, формула проверяет часть адресов перед запятой. Если они совпадают, то слово «Дубликат»

возвращается; если они не совпадают, то ничего не возвращается.

В результате копирования формулы вниз по столбцу (чтобы одна формула соответствовала каждому адресу) будет слово «Дубликат»

появляются рядом с теми адресами, которые соответствуют первой части предыдущего адреса. Затем вы можете понять, что вы хотите делать с найденными дубликатами.

Другой вариант — использовать макрос для определения возможных дубликатов.

Существует множество способов создания макроса для определения дубликатов; показанный здесь просто проверяет первые X символов «ключевого» значения по диапазону и возвращает адрес первой совпадающей ячейки.

Function NearMatch(vLookupValue, rng As Range, iNumChars)

Dim x As Integer     Dim sSub As String

Set rng = rng.Columns(1)

sSub = Left(vLookupValue, iNumChars)

For x = 1 To rng.Cells.Count         If Left(rng.Cells(x), iNumChars) = sSub Then             NearMatch = rng.Cells(x).Address             Exit Function         End If     Next     NearMatch = CVErr(xlErrNA)

End Function

Например, предположим, что ваши адреса находятся в диапазоне A2: A100.

В столбце B вы можете использовать эту функцию NearMatch для возврата адресов возможных дубликатов. В ячейку B2 введите следующую формулу:

=NearMatch(A2,A3:A$100,12)

Первый параметр функции (A2) — это ячейка, которую вы хотите использовать в качестве «ключа». Первые 12 символов этой ячейки сравниваются с первыми 12 символами каждой ячейки в диапазоне A3: A $ 100. Если ячейка найдена в том диапазоне, в котором совпадают первые 12 символов, тогда функция возвращает адрес этой ячейки. Если совпадений не обнаружено, возвращается ошибка # Н / Д. Если вы копируете формулу из B2 вниз, в ячейки B3: B100, каждый соответствующий адрес в столбце A сравнивается со всеми адресами под ним. В результате вы получите список возможных дубликатов в исходном списке.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2782) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Removing_Duplicates_Based_on_a_Partial_Match [Удаление дубликатов на основе частичного совпадения].