Процедура поиска значений

[Bur]

Имеется таблица с большим объемом данных (~66K строк), и ~10 столбцов. Например: Один из столбцов имеет статус индикатора (т. е. по нему нужно понимать с какой строкой работать) "New" (столбец 7). Задача стоит в том, чтобы для каждой строки с "New" выполнить поиск подобных строк, и в случае обнаружения подобной строки, у которой во 2 и 3 столбце в названии отсутствуют вопросы "??". Проблема заключается в том, что если подходить к решению задачи в лоб, то из-за большого объема данных, время выполнения макроса очень велико.

Sheets(1).Select
For i=1 to 10
If Cells(i,7)="New" Then
For j=2 to 10
If Cells(i,5)=Cells(j,5) Then
If Cells(i,7)<>"New" and Instr(Cells(j,2),"??")=0 Then
cells(j,2)=Cells(i,2)
Cells(i,3)=Cells(j,3)
End if
End if
Next j 
Next i

Как можно оптимизировать процесс, с учетом того что объем данных довольно большой ~66K строк?

[EducatedFool]

Оптимизировать несложно.
Выложите пример файла (хватит 10-100 строк с данными)

Обязательно макрос? или, может, формула в дополнительном столбце подойдёт?

Столбцы 5 и 7, по которым производится поиск, а также столбцы 2 и 3, где вносятся изменения, не могут оказаться в другом месте? (порядок столбцов не меняется?)

и в случае обнаружения подобной строки, у которой во 2 и 3 столбце в названии отсутствуют вопросы "??"

А если таких подходящих строк окажется несколько?
Брать данные из первой попавшейся?

[doober]

Файл оригинал какого расширения xls или xlsx

[Bur]

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

Оптимизировать несложно.
Выложите пример файла (хватит 10-100 строк с данными)

Выложил в первом сообщении

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

Обязательно макрос? или, может, формула в дополнительном столбце подойдёт?

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

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

Столбцы 5 и 7, по которым производится поиск, а также столбцы 2 и 3, где вносятся изменения, не могут оказаться в другом месте? (порядок столбцов не меняется?)

Нет, порядок не меняется

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

А если таких подходящих строк окажется несколько?
Брать данные из первой попавшейся?

Берется первое значение, потому как предполагается что все остальные уже редактированные и идентичны.

[Bur]

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

Файл оригинал какого расширения xls или xlsx

Оригинал xlsx, потому как строк >66K

[EducatedFool]

Sub test()
    Dim ra As Range: Set ra = Range("a1").CurrentRegion
    arr = ra.Value    ' считываем диапазон в массив
    For i = LBound(arr) To UBound(arr)
        If arr(i, 7) = "New" Then
            For j = LBound(arr) To UBound(arr)
                If arr(i, 5) = arr(j, 5) Then
                    If arr(j, 7) <> "New" Then
                        If InStr(1, arr(j, 2) & arr(j, 3), "??") = 0 Then
                            arr(j, 2) = arr(i, 2)
                            arr(j, 3) = arr(i, 3)
                        End If
                    End If
                End If
            Next j
        End If
    Next i
    ra.Value = arr    ' заносим результаты обратно на лист
End Sub

Пример в файле: http://excelvba.ru/XL_Files/Sample__...__17-46-42.zip

[Bur]

EducatedFool, Спасибо!
Есть-ли эффективный способ по подкрашиванию ячеек?
Я сделал так:

For i = 2 To UBound(arr)
    If Cells(i, 7) = "New" Then
        Cells(i, 2).Font.ColorIndex = 3
        Cells(i, 3).Font.ColorIndex = 3
    End If
Next i

Не нравится время выполнения (((

[Hugo121]

Если у Вас "New" в >50% значений - то вероятно можно и так.
Ну а если иначе - то анализировать всё же следует значения массива, а уж как быстрее подкрасить на листе - это нужно подумать... может Union подключить, или в строку адреса собирать...

P.S. В 3 раза ускоряет отключение обновления экрана и пересчёта, и закрашивание диапазона:

        Range(Cells(i, 2), Cells(i, 3)).Font.ColorIndex = 3

Да, ещё проверил - сокращение количества диапазонов пропорционально ускоряет код, переход на массив особого эффекта не дал: 0.390625 vs 0.3125
Из 15000 строк 2515 New.

[Bur]

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