Поиск термина в Excel, возврат соответствующих терминов в массив

Question

Электронная таблица состоит из 5 столбцов и 600 строк. В столбце 1 (в качестве примера) есть 9 типов вирусов, все из которых являются смешанными, но все согласованы по правописанию. Другие 4 столбца являются характеристиками каждого из этих типов.

Таким образом, "вирус 1" имеет h 1, n 1, R 1,5 и был замечен на 6 неделе

и это продолжается для 600 строк с вирусом '1 до 9 смешанных

На отдельном рабочем листе я бы хотел, чтобы Excel вытащил все данные для Virus 3. Таким образом, он должен искать во всей электронной таблице все в столбце 1, называемом "Вирус 3", и возвращать остальные 4 значения для него.

Есть простой процесс сортировки и вставки, но я не могу этого сделать, так как это должен быть живой документ. Каждую неделю приходят новые данные, которые смешиваются, я хотел бы, чтобы мои миньоны вырезали и вставляли эти новые данные в мастер-лист, а затем смотрели на 9 других электронных таблиц, которые я создал, а также линии тренда, которые я бы разработали.

Answer 1

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

=IFERROR(INDEX(Sheet1!$A$2:$E$600,SMALL(IF(Sheet1!$A$2:$A$600=A2,ROW($A$2:$A$600),0),COUNTIF(Sheet1!$A$2:$A$600,"<>"&A2)+ROW(A2)-1)-1,2),"")  

Вы должны одновременно нажать Ctrl+Shift+Enter для каждой формулы, а затем перетащить ее вниз в тот же столбец

2 в конце представляют столбец 2 на листе 1, где у вас есть данные
A2:E600 все ваши данные в исходном листе (5 столбцов и 600 строк) изменяют их по мере необходимости
A2:A600 первый столбец
If вернет Row of the Virus, если найден, или 0, если нет
Счетчик несовпадающих строк
Малый найдет наименьшую соответствующую строку без 0
Строка (A2), чтобы иметь наименьшую n-ую строку для перемещения индекса при ее перетаскивании
Индекс вернет значение в той же строке вируса и столбце 2 для приведенной выше формулы

For column 3 use:  


=IFERROR(INDEX(Sheet1!$A$2:$E$600,SMALL(IF(Sheet1!$A$2:$A$600=A2,ROW($A$2:$A$600),0),COUNTIF(Sheet1!$A$2:$A$600,"<>"&A2)+ROW(A2)-1)-1,3),"")

For column 4 use:  


=IFERROR(INDEX(Sheet1!$A$2:$E$600,SMALL(IF(Sheet1!$A$2:$A$600=A2,ROW($A$2:$A$600),0),COUNTIF(Sheet1!$A$2:$A$600,"<>"&A2)+ROW(A2)-1)-1,4),"") 



For column 5 use:   


=IFERROR(INDEX(Sheet1!$A$2:$E$600,SMALL(IF(Sheet1!$A$2:$A$600=A2,ROW($A$2:$A$600),0),COUNTIF(Sheet1!$A$2:$A$600,"<>"&A2)+ROW(A2)-1)-1,5),"")

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