У Фарриса есть рабочий лист с адресами. Некоторые адреса очень близки к одинаковым, например, почтовый адрес такой же, и отличается только часть адреса, связанная с номером набора. Например, одна строка может иметь адрес «85 Seymour Street, Suite 101», а другая строка может иметь адрес «85 Seymour Street, Suite 412». Фаррис задается вопросом, как удалить дубликаты в списке адресов на основе частичного совпадения – на основе только адреса улицы и игнорирования номера набора.
Самое простое решение – дополнительно разделить адреса на отдельные столбцы, так что номер набора находится в отдельном столбце. Вы можете сделать это, выполнив следующие действия:
- Убедитесь, что справа от столбца адреса есть пустой столбец.
- Выделите ячейки которые содержат адреса.
- Выберите «Текст в столбцы» в меню «Данные». Excel запускает мастер преобразования текста в столбцы. (См. Рис. 1.)
- На первом этапе мастера убедитесь, что выбран параметр с разделителями, затем нажмите кнопку «Далее».
- На втором этапе мастера убедитесь, что установлен флажок “Запятая”, затем нажмите “Далее”.
- На третьем этапе мастера нажмите “Готово”.
Рис. 1. Мастер преобразования текста в столбцы.
Уличный адрес теперь должен находиться в исходном столбце, а ранее пустой столбец должен теперь содержать все, что было после запятой в исходных адресах. Другими словами, номер квартиры находится в отдельной колонке. Когда ваши данные находятся в этом состоянии, можно легко использовать фильтрацию для отображения или извлечения уникальных уличных адресов.
Если вы не хотите постоянно разделять адреса на два столбца, вы можете используйте формулу для определения дубликатов. Предполагая, что список адресов отсортирован, вы можете использовать формулу, подобную следующей:
= IF (OR (ISERROR (FIND («,», A3)), ISERROR (FIND («, «, A2))),» «, IF (LEFT (A3, FIND («, «, A3)) = LEFT (A2, FIND («, «, A2)),» Duplicate «,» «))
В этой формуле предполагается, что проверяемые адреса находятся в столбце A, а эта формула размещена где-то в строке 3 другого столбца. Сначала он проверяет, есть ли запятая в адресе в текущей строке или в адресе в предыдущей строке. Если в любом из адресов нет запятой, предполагается, что дубликата нет. Если в них обоих есть запятая, формула проверяет часть адресов перед запятой. Если они совпадают, возвращается слово «Дубликат»; если они не совпадают, то ничего не возвращается.
В результате копирования формулы вниз по столбцу (чтобы одна формула соответствовала каждому адресу) будет слово «Дублировать» появляются рядом с теми адресами, которые соответствуют первой части предыдущего адреса. Затем вы можете выяснить, что вы хотите делать с найденными дубликатами.
Другой вариант – использовать макрос для определения возможных дубликатов. Существует множество способов создания макроса для определения дубликатов; показанный здесь просто проверяет первые X символов «ключевого» значения по диапазону и возвращает адрес первой совпадающей ячейки.
Функция 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 сравнивается со всеми адресами под ним. В итоге вы получите список возможных дубликатов в исходном списке.
Источник: