Эта задача возникает при необходимости разнести поток исходных данных по разным строчкам таблицы например столбец товаров, которым соответствует столбец номеров заказов разнести в таблицу заказов построчно или в список в одной ячейке через запятую.
Эксель позволяет найти только одну запись из диапазона данных, а надо все.
Для этого требуется добавить в таблицу дополнительный столбец, содержащий ключ и номер повтора, а потом просто разбросать заказ по столбцам каждый товар в свою ячейку.
Рассмотрим на примере надо заполнить колонку "товары" для строки каждого заказа:
введём дополнительное текстовое поле "ключ" и занесём в него и номер заказа и номер совпадения.
Номер совпадения с заказом считаем как количество появлений заказа в строках от начала таблицы получим выражение:
=СЧЁТЕСЛИ( $A$3:A3;A3 )
для первой ячейки колонки "ключ" можно взять такое выражение:
=A3&"_"&СЧЁТЕСЛИ( $A$3:A3;A3 )
на остальные ячейки эту формулу растягиваем:
выделяем дополнительный диапазон в который разнесём каждый товар в строку заказа в соответствии с номером совпадения по формуле:
=ИНДЕКС( $B$3:$B$10; ПОИСКПОЗ( $E3&"_"&( СТОЛБЕЦ() -СТОЛБЕЦ($H3) +1); $C$3:$C$10; 0))
для заполнения пустых полей добавим функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА( ИНДЕКС( $B$3:$B$10; ПОИСКПОЗ( $E3&"_"&( СТОЛБЕЦ() -СТОЛБЕЦ($H3) +1); $C$3:$C$10; 0)); "")
теперь можно просто соединить ячейки одной строки функцией ОБЪЕДИНИТЬ (доступной олько в последних версиях Эксель)
=ОБЪЕДИНИТЬ(", ";ИСТИНА;H3:K3)
для прежних версий придется перечислить все ячейки диапазона, учитывая, что могут быть пустые ячейки:
=H3&ЕСЛИ( I3=""; "";" , "&I3)&ЕСЛИ(J3=""; "";", "&J3 )&ЕСЛИ(K3=""; ""; ", "&K3)
в результате получим готовое решение: