поиск макросом дубликатов в таблице эксель

[brokoli]

Приветствую

появилась необходимость поиска повторяющихся улиц с номерами домов в таблице эксель с помощью макроса

Итак есть столбец "улица", рядом столбец "дом". Есть еще другие Столбцы в которых есть информация.
Если есть 2 записи в которых улица и номер дома идентичны, то подсветить красным цветом чтобы менеджер мог удалить лишнюю запись.
Так как дубликатов быть не должно.
Таблица состоит из 20 000! записей(строк)


Как можно было бы организовать алгоритм обхода так, чтобы это работало максимально производительно(быстро)

[EducatedFool]

Прикрепите к сообщению пример файла (можно не весь, но столбцы с улицами и домами должны присутствовать)

А 20 000 строк - это не так уж и много.
Или у Вас комп очень медленный?

[brokoli]

комп вроде нормальный: два ядра по три гигагерца.

база во вложении
а также моя версия макроса))

[Юнлинг]

Мне кажется макрос написан достаточно компактно и должен работать быстро. Единственное изменение которое сделал это поместил перед вторым циклом

         range2 = sStartCol & iNextRow & ":" & sStartCol & iLastRow
    '   ===
        For Each innercell In Range(range2)
        'MsgBox innerñell.address
            If R = getTrimCell2(innercell.address) Then
                Call paint_red(cell)
                Call paint_red(innercell)
            End If
        Next

условие if cell.Interior.ColorIndex <> 3 then тело второго цикла.

В этом случае будут пропускаться ячейки окашенные в красный цвет.
А насколько повыситься быстродействие суди сам

[brokoli]

думаю что проверка цвета почти ничего не даст..

вот щас подумал функцию getTrimCell2 надо переписать.

зачем каждый раз делать конкатенацию
Trim(Range(adr).Value) & Trim(Range(adr).Offset(0, 1).Value)

надо просто отдельно сравнивать парами
street1 = street2 and number1 = number2

[IgorGO]

а макрос - это обязательно? просто подсветить одинаковые условным форматированием недостаточно?

[brokoli]

Сообщение от IgorGO

а макрос - это обязательно? просто подсветить одинаковые условным форматированием недостаточно?

отличная идея!!!

только вот в процессе реализации возникает сообщение такое
"недостаточно системных ресурсов для отображения диалогового окна"
и как его вылечить ?

у меня офис 2007 rus крякнутый

[IgorGO]

идея плохая.
я посмотрел файл. оказалось порядка 250 строк - это повторы. слово обед 90 раз встретилось, 60 пустых строк...

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

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

[Юнлинг]

Сообщение от brokoli

думаю что проверка цвета почти ничего не даст..

вот щас подумал функцию getTrimCell2 надо переписать.

зачем каждый раз делать конкатенацию
Trim(Range(adr).Value) & Trim(Range(adr).Offset(0, 1).Value)

надо просто отдельно сравнивать парами
street1 = street2 and number1 = number2

При проверке цвета лишний раз не будет срабатывать второй цикл (повторного поиска одинаковых значений). Если цвет будет совпадать с красным незачем повторно искать эти же значения. Поиск уже осуществлен.
И подумай вот о чем, если первое из повторенных значений находиться ну скажем в первой сотни - цикл будет крутиться более 19900 что займет достаточно времени. Чем не сокращение времени поиска повторений

Я бы ввел дополнительное поле уже проверенных знаечний в какой нибудь из скрытых ячеек. Тогда первый цикл будет идти долго а вот поиск новых значений гораздо меньше. Надо будет найти совпадение вновь введенных занчений уже с отработанными. а это существенно меньше чем выполнять весь цикл

[EducatedFool]

Такие темы встречались уже множество раз.

Немного переделал макрос из этой темы.

Вот что получилось... (см. вложение)

(добавлено)

Как можно было бы организовать алгоритм обхода так, чтобы это работало максимально производительно(быстро)

Мой макрос обрабатывает данные далеко не самым эффективным образом (перебором строк).
Но формирование выборки уникальных адресов происходит за несколько секунд (создаётся новый лист с выборкой).

При желании можно уменьшить время обработки до 1 секунды (если сразу считывать все адреса в массив, обрабатывать массив, а потом записывать массив на новый лист)

Можно удалять также пустые и полупустые строки (если Вы сформулируете требования к удаляемым строкам)