Сравнение 2-х списков в MS EXCEL

Сравним 2 списка содержащих ТЕКСТОВЫЕ повторяющиеся значения.

Пусть в столбцах A и В имеется два списка с повторяющимися значениями.

Задача

Сравнить содержимое обоих списков.

Прежде чем начать сравнение списков определимся с методикой сравнения:

1. Списки считаются полностью совпадающими, если списки их уникальных значений совпадают и каждое уникальное значения имеет одинаковое количество повторов (сортировка может быть любая);

2. Списки считаются частично совпадающими, если списки их уникальных значений совпадают, но количество повторов каждого элемента м.б. любое;

3. Списки считаются не совпадающими, если списки их уникальных значений не совпадают (значения, которые есть в одном списке, в другом отсутствуют).

Решение

Создадим для удобства 2 Динамических диапазона Список1 и Список2, которые будут ссылаться на диапазоны ячеек, содержащие значения в соответствующих списках.

Чтобы сравнить списки сделаем следующее (см. Файл примера):

• Сформируем в столбце D единый список уникальных значений для обоих списков (см. статью Отбор уникальных значений из двух диапазонов) с помощью формулы массива:
  =ЕСЛИОШИБКА(ЕСЛИОШИБКА(
  ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список1);0));
  ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$4:D4;Список2);0)));
  "")
• Подсчитаем количество вхождений каждого уникального значения в оба списка с помощью формул =СЧЁТЕСЛИ(Список1;D5) и =СЧЁТЕСЛИ(Список2;D5)
• Если количество повторов каждого уникального значения в обоих списках совпадает, то формула в ячейке Е1 =СУММПРОИЗВ(ABS(E5:E34-F5:F34)) вернет 0, т.е. списки являются полностью совпадающими (их заголовки окрашиваются зеленым цветом);
• Если все значения из списка уникальных присутствуют в обоих списках, то формулы в ячейках Е2 и F2 =СЧЁТЕСЛИМН($D$5:$D$34;"*?";E5:E34;0) и =СЧЁТЕСЛИМН($D$5:$D$34;"*?";F5:F34;0) вернут 0. Если в ячейке Е1 не 0, а в ячейках Е2 и F2 0, то списки являются частично совпадающими (их заголовки окрашиваются оранжевым цветом) 
• Если хотябы одна из вышеуказанных формул (ячейки Е2 и F2) возвращают не 0, то списки считаются не совпадающими.

Тестируем

1. В файле примера (диапазоны (А26:B33; А36:B41; А44:B49) имеется 3 пары списков каждого типа: полностью совпадающие; частично совпадающие; не совпадающие.

2. Вставляя по очереди указанные пары списков в диапазон A5:B19, результат получим в виде цвета заголовка исходных списков (полностью совпадающие списки имеют заголовки зеленого цвета; частично совпадающие - желтого; не совпадающие - красного). Цвет заголовков списков определяется Условным форматированием.