[решено] Сравнение списка VBA

[includer-test]

Всем доброго времени суток.

Стоит задача для сравнения большого списка в Excel. Алгоритм понимаю, как это выразить в VBA не представляю понятия.
Так вот. Имеется столбец с фамилиями А и три столбца с фамилиями B, С, D.
Необходимо все фамилии из столбца А сравнить со столбцами фамилий B C D, если фамилия из А не встречается ни в одном из столбцов B C D, то записать эту фамилию в столбец E.

Формулами я знаю как это сделать, но хочется сделать это именно через VBA, т.к. список огромный.

[Iska]

Не вполне понятен Ваш алгоритм. Приложите образец рабочей книги.

[includer-test]

Грубо говоря, есть таблица

Иванов
Петров
Сидоров

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

Иванов
Петров
Сидоров

Формулой в Excel я это делаю так. Считаю количество вхождений ячейки А2 в первый диапазон.(лист Апрель от A2:A42734)
=(СЧЁТЕСЛИ(Апрель!A2:A42734;Январь!A2))
Затем
=(СЧЁТЕСЛИ(Март!A2:A42734;Январь!A2))
Затем
=(СЧЁТЕСЛИ(Февраль!A2:A42734;Январь!A2))

Ну а потом просто, фильтрую по 0 все три столбца, вот они мои люди, которые не входят ни в один диапазон.

Но мне надо это делать например нажатием кнопки.

[Iska]

includer-test, Вам сложно приложить образец рабочей книги? Мне гораздо больше времени потребуется, чтобы воссоздать её вид вручную. И это не гарантирует её точного вида, такого же, как у Вас.

[includer-test]

Приношу извинения.
Не стал грузить весь файл, т.к. там личная информация, да и строк там более 48 т. Поэтому подготовил свой пример, в котором все понятно.

100513

[azbest]

includer-test,
А если у Вас в столбце А - Иванов Иван Иваныч, а в столбце B - Иванов Иван Ильич, а в столбце C - Иванов Иван Кузьмич. Фамилии одинаковые, а Имена и Отчества могут быть разные...

и как сравнивать: подряд, перебирая все 3 столбца по строке, или поочереди - сравнивая со вторым столбцом, потом с третьим и потом с четвертым столбцами.

ps: пример сохранили бы в более раннем формате - не открывается он у меня.

[includer-test]

Если имя и/или отчество не совпадает со всеми тремя столбцами, то записать в столбец с результатом, это мы как раз и ищем.
Сравнивать думаю лучше перебирая ФИО из первого столбца поочереди с каждым столбцом. Если будет четкое совпадение, то выходить из перебора и брать следующую ФИО.
Пересохранил...100535

[Iska]

includer-test, для формата *.xls (97/2003) работает в лучшем виде такой код (запрос исключения из запроса на объединение):
Option Explicit

Sub SelectFIOFromListNotInTables()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = 1

Dim objConnection As Object
Dim objRecordset As Object
Dim intRow As Integer

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 8.0;HDR=Yes;"";"
objRecordset.Open _
"SELECT FIO FROM [Эталон$] " & _
"WHERE FIO NOT IN (" & _
"SELECT FIO FROM (" & _
"SELECT FIO FROM [Февраль$] UNION SELECT FIO FROM [Март$] UNION SELECT FIO FROM [Апрель$]" & _
")" & _
")" & _
";", objConnection, adOpenStatic, adLockOptimistic, adCmdText

intRow = 1

Do Until objRecordset.EOF
intRow = intRow + 1

ThisWorkbook.Worksheets.Item("Эталон").Cells.Item(intRow, 7).Value = objRecordset.Fields.Item("FIO").Value
objRecordset.MoveNext
Loop

objRecordset.Close
Set objRecordset = Nothing

objConnection.Close
Set objConnection = Nothing
End Sub

(файл рабочей книги прилагается: 100558; обратите внимание, что для корректной работы я убрал лишние строки [с объяснениями] из листа «Эталон»).

К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте):
Excel 2007 Connection String Samples - ConnectionStrings.com (http://www.connectionstrings.com/excel-2007)
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center (http://www.microsoft.com/en-us/download/details.aspx?id=13255)

[Delirium]

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

[includer-test]

не вижу связи. И запись макросов еще никто не отменял.
Включаем запись, вбиваем формулу в нужный столбец и дважды жмем на правый нижний угол ячейки с формулой. Сделается автозаполнение по всем данным, пусть их хоть сотни тысяч. Жмем остановить запись макроса и получаем готовый код. »

Автоматическое заполнение делается, но сдвигается диапазон сравнения на один шаг. Как зафиксировать диапазон я не разобрался. Если Вы знаете как обойти это, то буду признателен за подсказку.

К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте):
Excel 2007 Connection String Samples - ConnectionStrings.com
Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Center »

Спасибо за помощь. Но что-то дома у меня этот макрос на Office 2010 никак не запустился. Сейчас еще на работе попробую, руки еще не дошли просто.

[includer-test]

(файл рабочей книги прилагается: Sample.7z; обратите внимание, что для корректной работы я убрал лишние строки [с объяснениями] из листа «Эталон»).
К сожалению, мне никак не удалось из-под Microsoft Excel 2003 добиться корректной работы непосредственно с форматом *.xlsx/*.xlsm (2007/2010) — аналогичный запрос там попросту не срабатывал. Если хотите попробовать сами поиграться — вот Вам ссылки на строку подключения для нового провайдера и на самого провайдера (если у Вас новый Microsoft Office — то сей провайдер должен был устанавливаться в комплекте): »

На работе все заработало, наверно пакеты обновлений нужные автоматом ставились. Большое спасибо. Решено.

[Iska]

Но что-то дома у меня этот макрос на Office 2010 никак не запустился. »
Я же писал, что для свежих Microsoft Office нужен новый провайдер (который, по идее, должен устанавливаться вместе с самим Office; я же его загружал и устанавливал отдельно) и другая строка подключения (ссылку: Excel 2007 Connection String Samples - ConnectionStrings.com (http://www.connectionstrings.com/excel-2007) я приводил выше).

[Delirium]

но сдвигается диапазон сравнения на один шаг »
Значит надо зафиксировать либо столбец либо строку. Для этого встаем в строку формул и жмем F4 на имени ячейки. ПОявятся знаки $. Это признак абсолютной ссылки.