сравнить 2 массива данных с частичным совпадением в ячейках

[Nitron]

Добрый день!
Прошу помощи, заранее спасибо!
Дано:
Есть 2 файла, в 1м файле в столбце Е указаны просто города (Санкт-Петербург например), во 2м файле в столбце D указаны адреса в формате: ул. Вятская, Санкт-Петербург, дом 1.
Вопрос:
Как сравнить данные файлы и вывести значения находящееся в столбце F второго файла, при условии присутствия городов из первого файла в строках второго файла.

ПС: Пробовал ВПР с частичным совпадением но почему то не сработало, читал что надо сделать сортировку чтобы ВПР работал корректно, но не помогло, да и сортировку делать не желательно.

[cheshiki1]

файл пример покажите.

[Nitron]

файл пример покажите.

к сожаление файл не могу, т.к. файл с конфиденц инфой, могу сам попробовать сделать, но не факт что будет корректно

[Nitron]

Хотя, вот своял 2 файла, впр не работает...

[zs]

Здравствуйте, Nitron. Если указанный формат адреса соблюдается, то
=ЕСЛИ(СЧЁТЕСЛИ(A1:A3;ПСТР(Лист2!A1;НАЙТИ(",";Лист2!A1)+2;5)&"*")>0;Лист2!A1;"")
С уважением, ZS5)

[Nitron]

не во всех ячейках есть запятая чтобы по ней искать

[zs]

во 2м файле в столбце D указаны адреса в формате: ул. Вятская, Санкт-Петербург, дом 1.

?

[zs]

Необходимо форматировать

[IKor]

На правах концепции:
- создать список уникальных названий городов и разместить его в шапке доп. таблицы ВТОРОГО файла (несколько новых столбцов)
- В каждом из новых столбцов записать формулу вида: =НАЙТИ(B1;A2;1)
- Затем скрыть ошибки (или доп. столбцы целиком) и в столбце F обработать результат из доп. столбцов

[Nitron]

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

[IKor]

Вам в любом случае придется искать уникальные названия городов и формировать отдельный список. Т.к. если этого не сделать, то придется проводить много или ОЧЕНЬ много лишней работы (проверок). Это можно сделать автоматически, например, при помощи доп. столбца и функций: СЧЁТЕСЛИ(), НАИБОЛЬШИЙ() - если сами не справитесь, то поможем.

Относительно способов самих проверок наличия городов в тексте:
- Сама формула проста - я приводил ее ниже, но сложность заключается в представленнии и анализе результата ее работы
- выше я предлагал выводить результаты в отдельных столбцах для каждого из уникальных названий городов
- другой способ - использование {формул массива}, позволяющих виртуально сформировать дополнительные столбцы и произвести некоторые аналитические действия над их значениями. Формула не получится простой, и я не уверен, что ее обсчет будет достаточно быстрым для большого документа...
- и наконец, можно написать собственную формулу на VBA под Ваш конкретный случай - это будет наиболее быстро работающий вариант.

Да, забыл - есть вариант руками упростить исходные данные - MS Excel имеет ряд инструментов для этого, например "Текст по столбцам".

Необходимо форматировать