Вроде VLOOKUP, но с большим

Question

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

Итак, на листе 1 у меня есть список пациентов и их идентификационные номера (идентификационный номер пациента уникален): идентификационный номер пациента, имя пациента

Затем на листе 2 у меня есть список заказов на продажу (идентификатор заказа на продажу уникален, идентификатор пациента - нет): идентификатор пациента, идентификатор заказа на продажу, дата заказа на продажу

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

Итак, лист 1 будет выглядеть следующим образом: идентификатор пациента, имя пациента, дата заказа на продажу 1, дата заказа на продажу 2, ноль (если совпадений больше нет).

Я прочитал все, что могу найти в VLOOKUP и INDEX/MATCH, и не могу найти функцию, которая работает, когда на втором листе несколько совпадений (идентификатор пациента будет повторяться снова и снова для каждого заказа на продажу на листе). 2).

Пожалуйста помоги.

Answer 1

Это может быть решено путем подсчета заказов на одного клиента. Есть много вариантов того, как вы можете решить эту проблему, но вот один из них.

Шаг 1:

Сначала отсортируйте таблицу заказов по самым новым заказам.

Шаг 2:

Добавьте столбец в конце ваших заказов на продажу с формулой, которая подсчитывает номера клиентов. В новом столбце, второй строке, напишите формулу =Countif(B$2:B2,B2) .

Скопируйте формулу вниз.

B - столбец, в котором находятся номера клиентов.

Шаг 3:

Добавьте столбец с уникальным ключом для определения номера клиента и количества заказов. Во втором новом столбце второй строки напишите формулу =B2&" "&X2

B - номер клиента, а X - количество заказов, которое вы сделали на шаге 2.

Шаг 4:

В вашей таблице пациентов добавьте столбец, индекс которого совпадает с идентификатором самого нового заказа, второго нового заказа и т.д., Чтобы вернуть дату. Используйте IFERROR() для возврата пробела, если совпадения нет, и используйте абсолютные ссылки, чтобы легко скопировать формулы:

(Я пытался перевести формулу из моего норвежского Excel)

=IFERROR(INDEX('Order list'!$C:$C,MATCH('Patient list'!$A2&" "&'Patient list'!B$1,'Order list'!$Y:$Y,0)),"")

где C - столбец с датами, а Y - ключ на шаге 3.

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

Заказы:

Пациенты:

Answer 2

Вот решение, которое не требует вспомогательных столбцов или сортировки таблиц. Он просто использует одну относительно простую формулу массива.

Настройте два листа, как показано ниже, со вторым листом с именем Sheet2:

Массив введите (Ctrl+Shift+Enter) следующую формулу в ячейку C3 первого листа и скопируйте-вставьте / заполните в C3:G7:

{=IFERROR(SMALL(IFERROR(1/(1/((Sheet2!$A$2:$A$20=$A3)*(Sheet2!$C$2:$C$20))),""),C$2),"")}

Имейте в виду, что если заполнена последняя ячейка «Дата» таблицы «Пациенты» на листе 1, возможно, будет больше дат, которые не отображаются. В пример листа я добавил формулу в столбец справа от таблицы, чтобы предупредить, если это так:

Эта формула, массив, введенный в H3 и вставленный / скопированный в H3:H7 , имеет вид:

{=IF(ISERROR(SMALL(IFERROR(1/(1/((Sheet2!$A$2:$A$20=$A3)*(Sheet2!$C$2:$C$20))),""),G$2+1)),"","more")}