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

Я работаю над отчетом о проекте для работы, и я пытаюсь найти способ сравнить два списка кодов проектов, то есть «123456», и посмотреть, отсутствуют ли во втором списке какие-либо новые значения, которые были бы введены в первый список. Списки состоят из тысяч записей, и до сих пор люди делали это вручную (мне больно это знать), поэтому я пытаюсь сделать это автоматически.

Я пробовал использовать массив с формулой Index (Match (CountIF))), но я просто не могу заставить ее работать.

Пример

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

Я также пытался попробовать другие формулы, но списки могут состоять из тысяч записей, поэтому я не могу выполнить ячейку для сопоставления ячеек, так как список будет огромным (это или мои знания Excel недостаточно хороши, чтобы знать простое решение).

Любая помощь будет очень признательна.

  • Массив может быть не лучшим решением
  • Я проверил довольно много других решений, но они не совсем решают мою проблему, и у меня нет навыков их адаптации.

excel excel-formula vba excel-2010
person Jacob Crux    schedule 05.03.2018    source источник

Ответы (3)


arrow_upward
2
arrow_downward

Вот один из подходов с использованием VBA и массивов, который быстрее, чем через лист. Он проверяет каждый элемент в H, чтобы убедиться, что он присутствует в J (а не наоборот). Я предполагаю, что это то, что вы хотите.

Sub x()

Dim v1, v2, v3(), i As Long, j As Long

v1 = Range("H2", Range("H" & Rows.Count).End(xlUp)).Value
v2 = Range("J2", Range("J" & Rows.Count).End(xlUp)).Value

ReDim v3(1 To UBound(v1, 1))

For i = LBound(v1) To UBound(v1)
    If IsError(Application.Match(v1(i, 1), v2, 0)) Then
        j = j + 1
        v3(j) = v1(i, 1)
    End If
Next i

Range("K2").Resize(j) = Application.Transpose(v3)

End Sub

Использование поля ввода

Sub x()

Dim v1, v2, v3(), i As Long, j As Long

v1 = Application.InputBox("First list", Type:=8)
v2 = Application.InputBox("Second list", Type:=8)

ReDim v3(1 To UBound(v1, 1))

For i = LBound(v1) To UBound(v1)
    If IsError(Application.Match(v1(i, 1), v2, 0)) Then
        j = j + 1
        v3(j) = v1(i, 1)
    End If
Next i
Range("K2").Resize(j) = Application.Transpose(v3)

End Sub
person SJR    schedule 05.03.2018
comment
Спасибо SJR - я постараюсь попробовать. Я не уверен в VBA, но имею базовый опыт работы с ним в прошлом. Я буду информировать вас о том, как это происходит - person Jacob Crux; 06.03.2018
comment
Привет SJR - это прекрасно работает. Он выводит правильный список как легко выполняемый макрос. Как мне заставить код vba ссылаться на другой лист, я не могу заставить его показывать всплывающее окно, где я могу выделить каждый диапазон. Или я могу использовать инструмент именованного диапазона, чтобы выделить диапазоны, которые мне нужно сравнить? Изменить: сам сейчас изучаю это - person Jacob Crux; 06.03.2018
comment
Да, ниже я добавил альтернативный код, в котором вы выбираете два диапазона. И может быть расширен и для выходной ячейки. - person SJR; 06.03.2018
comment
В итоге я использовал v1 = Application.InputBox (выбрать весь диапазон списка проектов, получить объект диапазона, тип: = 8). Нашел в другой ветке - лишний текст это просто название окна. Спасибо за вашу помощь! Мой следующий шаг — добавить дополнительные критерии различения во время сравнения списков. Мне нужно будет использовать Vlookup, чтобы затем выбирать только строки, в которых есть «A» в ячейке 5 столбцов справа. Буду ли я прав, если предположу, что добавление оператора AND в предложение If с операцией vlookup будет работать? - person Jacob Crux; 06.03.2018
comment
Да, просто убедитесь, что вы включили другие столбцы при выборе соответствующего диапазона. - person SJR; 06.03.2018
comment
да - для массива таблиц для переменной Vlookup - Спасибо за вашу помощь, SJR, это было действительно здорово - person Jacob Crux; 06.03.2018

arrow_upward
2
arrow_downward

Решение по формуле. Обратите внимание, что я превратил первые два диапазона в Tables и изменил имена. В формуле используются структурированные ссылки. Это позволяет формуле автоматически обновляться при добавлении строк в будущем.

=IFERROR(INDEX(ProjList1[#Data],AGGREGATE(15,6,1/ISNA(MATCH(ProjList1[#Data],ProjList2[#Data],0))*ROW(ProjList1[#Data]),ROWS($1:1))-ROW(ProjList1[#Headers])),"")

Как это работает? Кратко:

  • MATCH генерирует массив #NA! ошибок или числа.
  • ISNA превращает это в массив TRUE/FALSE, где TRUE указывает на запись в таблице 1, которой НЕТ в таблице 2.
  • Умножение этого массива на массив строк списка проектов возвращает массив сообщения об ошибке и номера строки.
  • AGGREGATE небольшая функция игнорирует возврат ошибки и дает восходящий список номеров строк
  • INDEX затем возвращает соответствующую запись из таблицы 1.
  • ROW(ProjList1[#Headers]) — это исправление, позволяющее расположить таблицу в любом месте рабочего листа и по-прежнему возвращать правильную строку.
person Ron Rosenfeld    schedule 05.03.2018
comment
Привет Рон, спасибо за подробный ответ. С точки зрения реализации этого в действии. Будут ли какие-то биты формулы, которые мне придется отредактировать/настроить для нужных мне параметров. То есть вы переименовали заголовки, чтобы упростить задачу (нужно ли без пробелов?). Также вы указали, что превратили два диапазона в таблицы. Можете ли вы определить, что вы подразумеваете под этим, поскольку я не знаком с этой терминологией - в сущности, как вы превращаете диапазоны в таблицу. Я знаком с именованными диапазонами, но не знаком с преобразованием диапазонов в таблицы - извините за это. - person Jacob Crux; 06.03.2018
comment
@JacobCrux Обсуждение таблиц Excel и способов их использования см. в обсуждении Дебры Даглиш Создание таблицы Excel< /а> - person Ron Rosenfeld; 06.03.2018

arrow_upward
0
arrow_downward

Не уверен, что вы пытаетесь настроить это так, чтобы оно автоматически обновлялось в будущем, но в качестве временной меры:

Столбец Countif рядом со списком 1, который проверяет, появляются ли они в списке 2... ... Подача в сводную таблицу, которая показывает только те, где значение countif равно 0, в поле «строка», чтобы удалить дублирование?

person banbourg    schedule 05.03.2018