Сопоставление двух списков против друг друга

Question

У меня есть два списка, список 1 и список 2. Оба списка имеют два столбца, столбец A и столбец B, значения в столбце A(ID) и B(значение) соответствуют друг другу. Мне нужно сравнить эти два списка друг с другом и найти несоответствия, когда они происходят.

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

Соответствие будет: Список 1: столбец A= том, столбец B=5. Список 2: столбец A= том, столбец B=5.

Несоответствие может быть следующим: Список 1: столбец A= том, столбец B=5. Список 2: столбец A= том, столбец B=2.

Проблема в том, что в списке 2 есть дубликаты столбца A, которые содержат различные соответствующие значения (столбец B). Мои правила: если между двумя списками есть ОДНО совпадение (даже если позднее в списке встречается несоответствие), пометьте его как "МАТЧ", но если нет совпадений ни для одного из идентификаторов (столбец А), то пометьте его как "MISMATCH".

Вот формула, которую я использую, она берет исходный идентификатор (столбец A) из LIST 1 и пытается найти совпадение или несоответствие из Списка 1 и Списка 2:

= ЕСЛИ (ВПР (А1, А: В,2,FALSE)= ВПР (А1, С:D,2,FALSE),"MATCH","НЕСОВП")

Я не могу просто удалить дубликаты, потому что они не просто дубликаты, это один идентификатор (столбец A) с несколькими значениями (столбец B) в одном списке, но формула, которую я сейчас использую, не будет учитывать мое правило, которое я хочу реализовать и я действительно не знаю, с чего начать или как составить формулу, которая будет понимать, что если идентификатор (столбец A) содержит одно совпадение, даже если есть другие несоответствия, пометить его как совпадение. Вторая проблема, с которой я столкнулся, - сообщить о ней таким образом, чтобы я мог иметь идентификатор (столбец A) и иметь его состояние (совпадение или несовпадение) в одном столбце без дубликатов. Я думаю, это связано с исходной проблемой.

Извините за длинное объяснение, но я ценю любую помощь заранее.

Пример данных:

List1:

Колонка А | ColumnB

Том | 1

Джон | 2

Фред | 3

Брэд | 4

Список 2:

Колонка А | Колонка Б

Том | 1

Джон | 5

Джон | 1

Фред | 1

Фред | 2

Фред | 3

Брэд | 2

(Желаемый результат) Список 3:

Колонка А | ColumnB

Том | MATCH *, потому что Tom имеет одинаковые значения в столбце B для обоих списков

Джон | MISMATCH * потому что у jon разные значения в столбце B для обоих списков за все время, когда его идентификатор отображается

Фред | MATCH *, потому что он имеет по крайней мере ОДНО совпадение для столбцов B в обоих списках, даже если есть некоторые несоответствия, вот где правило вступит в силу

Брэд | MISMATCH * потому что его значение в столбце B в обоих списках не совпадает

Пример снимка экрана: снимок экрана примера данных

Answer 1

Можно взглянуть на это так, чтобы это соответствовало списку, основанному на двух критериях. Простой способ сопоставления по нескольким критериям - это объединить (объединить) отдельные значения критериев вместе, а затем сделать то же самое для списка, который вы просматриваете. Следующая формула массива сделает это:

= ЕСЛИОШИБКА (ЕСЛИ (MATCH(A1&B1,$D$1:$D$7 и $E$1:$E$7,0),"MATCH"),"НЕСОВП")

NB - мои столбцы A и B содержат ваш список 1, мои столбцы D и E - ваш список 2.

Введите эту формулу в C1 и нажмите Ctrl + Shift + Enter (это превращает формулу в формулу массива), а затем перетащите ее вниз в ячейку C4.

Answer 2

Вы можете посчитать, сколько раз совпадение найдено с COUNTIF() функция. Затем заверните IF заявление вокруг него, чтобы сказать, когда счет больше > 0, это Match, иначе Mismatch, Смотрите скриншот ниже:

Вот формула у меня в ячейке D4 ниже: =IF(COUNTIF(H4:H10,B4&C4)>0,"Yes","No")

Вот формула у меня в ячейке H4 ниже: =F4&G4

Надеюсь это поможет!