Excel Vlookup для 2-го, 3-го, 4-го или 5-го текстового значения вместо 1-го соответствующего значения

Question

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

ID       Certification
0123     CPR
456      CPR
456      Nursing
456      Safety
789      Engineering
966      CPR
966      Safety

Таким образом, для идентификатора 0123 у них есть только одно, для извлечения будет только одно значение, но для идентификатора 456 мне понадобится лист Excel, в котором значения сертификации будут находиться в той же строке для идентификатора, но во столько раз, сколько будет Сертификаты есть:

ID    Certif1        Certif2
456    CPR           Safety 

Любые идеи будут оценены, так как значения всего текста. Я также использую Excel 10.

Answer 1

Вы можете построить преобразованную таблицу в два этапа.

Шаг 1: Создайте список уникальных идентификаторов.

Вы можете сделать это с помощью расширенного фильтра. Выберите столбец идентификаторов, включая заголовок, и нажмите «Расширенный фильтр» на ленте данных. Установите флажок « Unique Records Only и выберите « Copy to another location . Установите местоположение для выхода и нажмите ОК. Это даст вам список уникальных идентификаторов для строк вашей новой таблицы.

Шаг 2: Получите сертификаты, которые соответствуют каждому идентификатору в новых столбцах.

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

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$F2,ROW($B$1:$B$8),1000000),COLUMN()-6)),"")

где A1:B8 - предоставленные вами исходные данные,
F2 - это идентификатор в новой таблице, для которой вы ищете сертификаты, и
COLUMN()-6 равно 1 для столбца Certif1 , 2 для столбца Certif2 (вам нужно будет отрегулировать срок вычитания в соответствии с вашими данными).

После ввода выберите ячейку, щелкните внутри панели формул и нажмите Ctrl+Shift+Enter. Это введет формулу в виде формулы массива.

После этого заполните формулу вниз по столбцу. Затем заполните столько столбцов, сколько вам нужно (только 3 для ваших образцов данных, так как 456 имеет 3 сертификата). Это должно дать вам то, что вы хотите.

Образец:

Answer 2

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

Для примера слева перетащите идентификатор и сертификацию в область строк и выберите табличный макет. Удалите все итоги и промежуточные итоги.

Для примера слева перетащите ID в строку, а Сертификацию в область столбцов, а затем снова Сертификацию в область значений.