Извлечь совпадающие значения из двух столбцов в Excel

Question

У меня есть два списка людей, которые посетили определенное событие. Эти два списка были созданы двумя разными пользователями. Каждый посетитель имеет уникальный идентификатор посетителя, поэтому я извлек столбец идентификатора посетителя из каждого списка и вставил их рядом в новый лист. Что мне нужно сделать, это использовать какую-то формулу, которая будет отображать любые идентификаторы посетителей, которые появляются в обоих столбцах в третьем столбце. Короче говоря, мне нужно знать, сколько одинаковых посетителей было зарегистрировано двумя пользователями.

Один список также значительно длиннее другого, если это имеет значение. Идентификаторы в каждом списке также расположены не в каком-либо порядке.

В принципе, я хочу увидеть что-то вроде этого:

List from User 1        List from User 2        Matching IDs
    1129                      1781                   1129
    1200                      1743                   1776
    1525                      1129                   1200 
    1695                      1350                   1525
    1928                      1776
    1972                      1644
    1776                      1200
    1297                      1525
    1980                      1153

Я просмотрел несколько уроков, но ни один из них не соответствует нужному мне контексту. Какой правильный синтаксис для формулы, которая будет выполнять эту функцию?

Answer 1

Список из User1 в столбце A, Список из User2 в столбце B, в столбце C, начало в C2, напишите следующую формулу:

=IFERROR(INDEX($B$2:$B$10,SMALL(IF(COUNTIF($A$2:$A$10,$B$2:$B$10)>0,ROW($B$2:$B$10)-1,9999999999),ROW()-1)),"")

Формула массива нажмите Ctrl+Shift+Enter одновременно
Перетащите формулу вниз, она вернет идентификатор в случае совпадения (сопоставление будет следовать, а затем пустое) или пусто
A2:A10 - это Данные в столбце A без заголовка, (Перечислите user1) измените его на ваши ссылки на Данные.
B2:B10 - это Данные в столбце B без заголовка, (Перечислите user2) измените их, чтобы они соответствовали вашим ссылкам на Данные.
Сохраняйте $ для фиксированных ссылок
Countif найдет соответствующий идентификатор
If вернет номер строки при сопоставлении
Индекс вернет идентификатор, соответствующий наименьшей строке () при перетаскивании формулы

Обновление, соответствующее вашим ссылкам

=IFERROR(INDEX($E$5:$E$898,SMALL(IF(COUNTIF($C$5:$C$247,$E$5:$E$898)>0,ROW($E$5:$E$898)-4,999999999),ROW(A5)-4)),"")

Поскольку ваши данные начинаются в строке 5, я предполагаю, что ваша формула будет также начинаться в строке 5
Я изменил формулу, чтобы соответствовать этому

= ЕСЛИОШИБКА (ИНДЕКС ($ E $ 5:$ E $ 898, МАЛЫЙ (ЕСЛИ (СЧЕТЕСЛИ ($ C $ 5:$ C $ 247, $ E $ 5:$ E $ 898)> 0, СТРОКА ($ E $ 5:$ E $ 898) -4 , 999999999), СТРОКА (А5) -4)), "")

Answer 2

Vlookup (в столбце совпадающих идентификаторов) должен выполнить эту работу.

Формула что-то вроде этого = Vlookup(ячейка короткого списка, длинный список, 1, ЛОЖЬ)

False даст NA за отсутствие совпадений, просто отфильтруйте, чтобы получить ваш окончательный список.

Answer 3

VLOOKUP с IFERROR будет возвращать значения в новом столбце, только если значение существует в обоих столбцах. После этого вы сможете использовать фильтр данных для отображения только соответствия. Исходя из ваших данных, предположим, что "Пользователь 1" в столбце А и "Пользователь 2" в столбце Б:

= ЕСЛИОШИБКА (ВПР (В2, А: А, 1, FALSE), "")

Чтобы гарантировать результаты, таблицу необходимо отсортировать в порядке возрастания, но, поскольку существует только две точки данных, я назвал диапазон таблицы только как столбец A, а столбец поиска - как 1. Поскольку существует только одна точка поиска данных, она может иметь дело с неупорядоченными числами.

Answer 4

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

После этого урока (http://spreadsheetpro.net/comparing-two-columns-unique-values/) я смог сравнить мои два столбца и применить форматирование (в моем случае, зеленые выделения) к любым идентификаторам посещений в коротком столбце, который также появился в длинной колонке.