Поиск совпадений.

[riniks17]

Уважаемые форумчане. С Наступающим вас Светлым Праздником.
Вы уже научили меня как определить с помощью формул наличие совпадений по двум столбцам на листе 1 и листе 2. Возможно ли сделать это с помощью условного форматирования? Т.е. выделить цветом всех индивидумов на листе 1 которые встречаются на листе 2. И можно ли сделать это при помощи макроса? Уверен что можно. Поэтому и обращаюсь. Особая благодарность если код макроса будет с пояснениями. Заранее огромное спасибо и ещё раз с наступающими праздниками.

[aNJay]

Попробуй етот код:

Sub find_repeat()
Dim n1 As Integer, n2 As Integer, i As Integer, j As Integer, iRow As Integer
iRow = 2
n1 = Sheets("Лист1").Cells(2, iRow).End(xlDown).Row
n2 = Sheets("Лист2").Cells(2, iRow).End(xlDown).Row
For i = 2 To n1
    For j = 2 To n2
        If Sheets("Лист1").Cells(i, iRow).Value & Sheets("Лист1").Cells(i, iRow + 1).Value = Sheets("Лист2").Cells(j, iRow).Value & Sheets("Лист2").Cells(j, iRow + 1).Value Then 'повтор
'            Sheets("Лист1").Cells(i, iRow).Interior.Color = 65535
'            Sheets("Лист1").Cells(i, iRow + 1).Interior.Color = 65535
            Sheets("Лист1").Cells(i, iRow).Font.Color = -16776961
            Sheets("Лист1").Cells(i, iRow + 1).Font.Color = -16776961
        End If
    Next
Next
End Sub

[bize]

я не сильный знаток но вроде работает)

[Hugo121]

aNJay, забудьте по Integer вообще, ну а тут это явно ведёт к ошибке, если лист будет заполнен хотя бы на половину.
Ну и думаю это самый медленный код из возможных...

[riniks17]

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

aNJay, забудьте по Integer вообще, ну а тут это явно ведёт к ошибке, если лист будет заполнен хотя бы на половину.
Ну и думаю это самый медленный код из возможных...

Есть варианты?

[Hugo121]

Так будет побыстрее:

Sub compare()
    Dim a(), i&
    Application.ScreenUpdating = False

    With Sheets("Лист2")
        a = Range(.[C2], .Range("B" & .Rows.Count).End(xlUp)).Value
    End With
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a): .Item(a(i, 1) & "|" & a(i, 2)) = 0&: Next
        With Sheets("Лист1")
            a = Range(.[C2], .Range("B" & .Rows.Count).End(xlUp)).Value
        End With
        For i = 1 To UBound(a)
            If .exists((a(i, 1) & "|" & a(i, 2))) Then
                Sheets("Лист1").Range("B" & i + 1 & ":C" & i + 1).Font.Color = -16776961
            End If
        Next
    End With
    Application.ScreenUpdating = True

End Sub

Но конечно основной тормоз - это покраска. Хотя и это можно ускорить... если будет критично.

[riniks17]

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

Так будет побыстрее:

Sub compare()
    Dim a(), i&
    Application.ScreenUpdating = False

    With Sheets("Лист2")
        a = Range(.[C2], .Range("B" & .Rows.Count).End(xlUp)).Value
    End With
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a): .Item(a(i, 1) & "|" & a(i, 2)) = 0&: Next
        With Sheets("Лист1")
            a = Range(.[C2], .Range("B" & .Rows.Count).End(xlUp)).Value
        End With
        For i = 1 To UBound(a)
            If .exists((a(i, 1) & "|" & a(i, 2))) Then
                Sheets("Лист1").Range("B" & i + 1 & ":C" & i + 1).Font.Color = -16776961
            End If
        Next
    End With
    Application.ScreenUpdating = True

End Sub

Но конечно основной тормоз - это покраска. Хотя и это можно ускорить... если будет критично.

Поклон. Работает.
А не в тягость объяснить код? Хочу сам научиться так же.
Пардоньте, поменял на листе 2 фигурантов и они просто добавились в лист 1. Старые так и остались отформатированными. Хотелось бы обновлять по значениЯм на листе 2.

[riniks17]

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

я не сильный знаток но вроде работает)

Спасибо. Пока буду пользовать этот вариант.

[Hugo121]

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

По алгоритму кода (каждую строку описывать лениво):
1. берём данные второго листа в массив
2. циклом его перебираем и заносим критерии в словарь
3. берём данные первого листа в массив
4. циклом его перебираем и проверяем наличие критерия в словаре - если есть, то красим соответствующую строку.

Итого всего два прохода по данным (а не цикл в цикле), и по массивам, а не по ячейкам листа.
На примере конечно разницу не увидите, но попробуйте код хотя бы на 1000 строк. А лучше на 10000
Я бы не красил, а заполнял параллельный массив единицами, затем его выгрузил на лист рядом с данными. Преимущества - не крадёт время (думаю пара процентов общего времени, хотя не замерял), можно сортировать по этим значениям и под 2003, не меняет ничего в данных (там уже могут быть крашеные ячейки). Не нужно предварительно ничего очищать - старые метки затрутся при выгрузке массива

[riniks17]

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

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

По алгоритму кода (каждую строку описывать лениво):
1. берём данные второго листа в массив
2. циклом его перебираем и заносим критерии в словарь
3. берём данные первого листа в массив
4. циклом его перебираем и проверяем наличие критерия в словаре - если есть, то красим соответствующую строку.

Итого всего два прохода по данным (а не цикл в цикле), и по массивам, а не по ячейкам листа.
На примере конечно разницу не увидите, но попробуйте код хотя бы на 1000 строк. А лучше на 10000
Я бы не красил, а заполнял параллельный массив единицами, затем его выгрузил на лист рядом с данными. Преимущества - не крадёт время (думаю пара процентов общего времени, хотя не замерял), можно сортировать по этим значениям и под 2003, не меняет ничего в данных (там уже могут быть крашеные ячейки). Не нужно предварительно ничего очищать - старые метки затрутся при выгрузке массива

Панятно. Я тож ленивый. Буду гуглить каждую строчку и пытаться понять. А смысл задачи в том, что меняя фигурантов нв втором листе, иметь возможность выделять их на первом. Можно и параллельный массив, можно и перпендикулярный, главное результат. Надо на лист 2 скопировать список и отметить этих товарищей на первом листе. И так множество раз.