Как найти повторяющиеся значения на основе столбца в двух электронных таблицах и проверить, является ли строка точным совпадением

Question

У меня есть две таблицы Excel: Header2018 и Header2019.

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

Я хочу объединить все дублирующиеся записи (на основе столбца: asset_id) в новую электронную таблицу, а затем проверить, есть ли разница между остальными столбцами.

Пример заголовка 2018:

asset_id    Name    Country

2          Trent      CAN
3          Alex       CAN
4          Derek      CAN
5          Louis      CAN
6          Teresa     CAN

Заголовок 2019:

asset_id    Name    Country
4          Derek      CAN
5          Louis      USA
1          Kate       CAN
7          Pat        CAN
8          Steven     CAN
9          Tom        CAN

Итоговая таблица:

asset_id    Name    Country
4          Derek      CAN
5          Louis      CAN
4          Derek      CAN
5          Louis      USA

На основе полученной таблицы я хочу найти записи, в которых asset_id совпадает, но другой столбец отличается.

Заранее спасибо!

Answer 1

Для получения результата можно использовать Power Query.

1. Добавьте 2 таблицы в Power Query — в редакторе Power Query перейдите на главную страницу — Добавить 2 запроса:

2. Выберите столбец asset_id, перейдите в меню «Преобразование» — выберите «Группировать по» — выберите «Дополнительно» — добавьте агрегацию — введите имя для нового столбца и выберите «Все строки»:

3. Разверните таблицу и выберите Имя и Страна - Фильтровать столбец Количество >2, удалить столбец Количество - Закрыть и загрузить:

Answer 2

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

Лист 1:

• Формула в ячейке A2дляЗначение помощника:

=COUNTIF(Sheet2!$B$2:$B$7, B2)

---

Лист 2:

• Формула в ячейке A2:

=COUNTIF(Sheet1!$B$2:$B$6, B2)

Примечание.

• Если оба листа имеют несколько повторяющихся идентификаторов (больше 2) тогда вместоСЧЁТЕСЛИ, лучше использовать эту формулу, чтобы найтиДубликаты.

  =IF(ISERROR(MATCH(B2,Sheet2!B$2:B$7,0)),"","Duplicate")

• И вОсновной листкритерии в Cell E59будутДубликатвместо1.

---

Основной лист:

• Формула массива в ячейке G60:

  {=IFERROR(INDEX(Sheet1!$B$2:$D$6, SMALL(IF(COUNTIF($E$59, Sheet1!$A$2:$A$6), ROW(Sheet1!$B$2:$D$6)-MIN(ROW(Sheet1!$B$2:$D$6))+1), ROW(A1)), COLUMN(A1)),IFERROR(INDEX(Sheet2!$B$2:$D$7, SMALL(IF(COUNTIF($E$59, Sheet2!$A$2:$A$7), ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1), ABS(ROW(A1)-2)), COLUMN(A1)),""))}
  

• Закончите формулу сCtrl+Shift+Enter, заполнить вправо, затем вниз.

• В формуле массива ABS(ROW(A1)-2))используется для корректировкиНомер строкидля .Sheet 2​1

При необходимости вы можете скорректировать ссылки на ячейки в формуле.