Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Сравнение столбцов и вывод отдельным диапазоном совпадений
 
Всем доброго дня уважаемые форумчане!)))
У меня есть макрос для сравнения,он сравнивает столбцы A,B с E,F и далее выводит совпадения на лист 2
Вопрос:
как сделать так что бы он еще и закрашивал совпадения в столбе A,B и в столбце C писал номер строки с которой совпало в столбцах E,F
Строк может быть до 70 000,можно ли посмотреть по данному макросу,все ли правильно там написано,тк бывают подозрения что он не все сличат(
Пример во вложении,Заранее благодарен если получится помочь
 
Цитата
Den255 написал: сравнивает столбцы A,B с E,F
Уточните: A сравнивает с E, B сравнивает с F? Или как?
Цитата
Den255 написал: как сделать так что бы он еще и закрашивал совпадения в столбе A,B и в столбце C писал номер строки с которой совпало в столбцах E,F
Покажите в файле несколько строк нужного результата, выполненного вручную
Согласие есть продукт при полном непротивлении сторон.
 
Безусловно добавить столбец с номерами строк и раскрасить дубликаты можно с помощью VBA, однако всю Вашу задачу можно решить без макросов:
* используйте условное форматирование для подсветки дубликатов
* для определения номера дублированной строки можно использовать формулу:
Код
=СУММПРОИЗВ(($E$2:$E$59=$A2)*($F$2:$F$59=$B2)*СТРОКА($E$2:$E$59))
или (если возможны повторы дубликатов):
Код
{=МАКС(ЕСЛИ(($E$2:$E$59=$A2)*($F$2:$F$59=$B2);СТРОКА($E$2:$E$59);""))}

По имеющемуся столбцу с номерами сдублированных строк легко можно повторить таблицу дубликатов на втором листе (с помощью функции ИНДЕКС() и НАИМЕНЬШИЙ() )
 
так как будет очень большой обьем,возможно думаю лучше бы с помощью VBA?количество строк в столбцах сравнения так же будет разным
 
Цитата
Sanja написал:
Уточните: A сравнивает с E, B сравнивает с F? Или как?
АВ(как одно целое) и EF(как одно целое) должны полностью совпадать
Изменено: Den255 - 31 Авг 2018 16:09:35
 
IKor,если я протягиваю вашу формулу до 50000,то он не находит совпадения даже 1300 строке
 
Den255, Вам известен смысл слова "цитата"? Посмотрите на свой #5.
 
В столбах А и В (Е и F) одинаковое количество строк или разное?
 
На всякий случай продублирую ответ и в этой теме:

Доброе утро, Мотя!
Проверил различные сочетания Excel 2010 и операционных систем.
Загружал в различном порядке файлы Den255.
Вроде все работает как обычно, но есть как известные, так и не очень, особенности работы поиска-замены, которые на всякий случай опишу подробнее, возможно, что проблема именно в них.

1. Ранее используемые параметры.
Excel запоминает почти все параметры, используемые ранее при поисках-заменах, и использует их в последующих поисках-заменах. Причем не важно, вручную ли (Ctrl-F / Ctrl-H) или макросом (Find) задавались эти параметры. Сброс параметров поиска-замены происходит при закрытии Excel.
Пример: ранее в поиске задавался формат (Ctrl-F > Параметры > Формат > 'Использовать формат этой ячейки' или цвет заливки и  т.п.), а в текущем поиске интересуют только значения. Для корректной работы нужно сбросить формат поиска: Ctrl-F > справа на кнопке 'Формат' ткнуть в значок выпадающего списка и выбрать 'Очистить формат поиска'. Или макросом:
Код
Sub ОчиститьПолеПоискаИформат()
  Range("A1").Find What:="", SearchFormat:=False
End Sub

Это макрос очистит формат и поле поиска, что исключит и проблемы отображения строки поиска с символом новой строки СИМВОЛ(10), который вводится в текст ячейки при помощи Alt-Enter, а в поле поиска - с помощью Ctrl-J по п.2 ниже. Или который задается для поиска в VBA с помощью Chr(10) или vbLf. Подробнее - см. п2.

Ещё пример кода для замены символа новой строки на пробел с явным заданием критических параметров поиска (частичный, без форматов):
Код
Sub УдалитьПереносыСтрок()
  Dim Rng As Range
  Set Rng = ActiveSheet.UsedRange
  Rng.Replace What:=vbLf, Replacement:=" ", _
              LookIn:=xlValues, LookAt:=xlPart, _
              SearchFormat:=False, ReplaceFormat:=False
End Sub

Ну, и как мы уже уточняли когда-то, поиск с LookIn:=xlValues не ищет в скрытых ячейках, а в объединенных ищет ограничено  - только если после отмены объединения размер ячейки с текстом окажется не слишком мал.

2. Символ новой строки.
Чтобы ввести в поле поиска символ новой строки, можно использовать Ctrl-J. Или, при отключенном NumLock, зажать клавишу Alt и, не отпуская её,  на дополнительной цифровой клавиатуре набрать 010, а затем отпустить Alt.
Здесь есть одна особенность: та часть текста, которая окажется после позиции введенного символа новой строки в поле поиска, становится невидимой, как общеизвестный суслик. Это может смутить, так как и символа мигающего курсора на второй строке почти не видно и создается впечатление, что Excel завис.
Например, ищем строку "123"&СИМВОЛ(10)&"456".
Для этого на листе нажмем Ctrl-F и в поле поиска наберем 123456, а затем поставим курсор перед четверкой и нажмем Ctrl-J. Курсор окажется на 2-й невидимой строке перед четверкой, если присмотреться, то под единицей можно увидеть верхнюю часть мигающего курсора. Нажатием на стрелки вправо-влево можно перемещать курсор, а нажатие на стрелки вверх-вниз отобразят выпадающий список ранее вводимых текстов поиска, при этом символ новой строки отобразится в списке квадратиком. Из выпадающего списка можно выбрать любой ранее вводимый вариант текста для поиска.
Очистить поле можно при помощи клавиш удаления и забоя.

3. Формат ячеек для поиска.
Для надежного поиска в ячейках с текстом их формат должен быть либо Общий либо Текстовый.
Мне и Юрию М, например, известен случай, когда Find не находил нужный фрагмент текста в ячейках, случайно имеющих денежный формат с новым символом рубля, формат был применен ко всему столбцу, включая текстовый заголовок, который таким образом прятался от Find.

4. Особенности клавиши Alt.
Если в поле поиска нажать и отпустить клавишу Alt, то фокус переносится на шапку формы поиска.
После этого при нажатии Enter или (стрелок) Вверх / Вниз,  отобразятся  пункты управления формы с помощью клавиатуры. Например, там можно выбрать 'Переместить' и клавишами-стрелками перемещать форму, завершив перемещение нажатием Enter.
Если случайно нажата и отпущена клавиша Alt, то вернуть фокус с шапки внутрь формы можно еще одним нажатием и отпусканием Alt.

5. Программы-перехватчики клавиатуры.
Иногда горячие клавиши для своих целей используют некоторые установленные на компьютере программы. Например, обновление драйвера видео-карты Nvidia может автоматически установить перехват нажатия Alt-F11. При этом в Excel эта комбинация перестает работать. Приходится в настройках Nvidia вручную снимать флажок перехвата Alt-F11. Такие проблемы хуже всего, так как программ много, а Excel-то у нас один.

Возможно, что ответа на Ваш конкретный случай здесь нет, но без воспроизведения проблемы трудно угадать истинную причину.
Желаю Вам побольше здоровья и поменьше капризов от всяких там Alt и Find ))
Изменено: ZVI - 31 Авг 2018 12:23:59
Vladimir Zakharov
Microsoft MVP – Office Apps & Services
 
Здравствуйте, Владимир! Спасибо за интересные разъяснения!
Я всегда считал, что при поиске (Range.Find) по значению поиск производится по свойству Text ячеек. Из Вашего сообщения следует, что для объединенных ячеек это не так (проверил).
Владимир
 
Цитата
sokol92 написал:
Я всегда считал, что при поиске (Range.Find) по значению поиск производится по свойству Text ячеек
По чему зададите, по тому и будет искать. Зададите LookIn:=xlValues — будет по значениям, зададите LookIn:=xlFormulas — будет по формулам...
 
Цитата
Мотя написал:
В столбах А и В (Е и F) одинаковое количество строк или разное?
Резное
Цитата
ZVI написал:
На всякий случай продублирую  ответ  и в этой теме:
А можете наглядно показать,в файле который я отправлял как пример?
 
Den255!
Присылайте нормальный файл - без последствий Alt+Enter!
Я не хочу снова "ломать" свой Portable Excel 2010.  
Я Вам "забубеню" макрос с Вашей "Хотелкой"!  :D
 
Цитата
StoTisteg написал:
Зададите LookIn:=xlValues — будет по значениям, зададите LookIn:=xlFormulas — будет по формулам...
Посмотрите про xlValues здесь.
 
Цитата
Мотя написал:
Присылайте нормальный файл - без последствий Alt+Enter!
Вот))))Так подойдет???
 
Цитата
Den255 написал:
Так подойдет???
:D  
 
не травите мне душу=)))))))))))))))))
 
Мотя,Спасибо ВАМ ОГРОМАДНЕЙШЕЕ,отличная работа,а главное все так как нужно было.Очень вам Благодарен
Страницы: 1
Читают тему (гостей: 1)
Наверх