Индексировать и сопоставлять несколько столбцов

Question

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

я использую следующее решение, чтобы соответствовать первым критериям

=INDEX(CompanyName,MATCH(1,MMULT(--(Attendee=G4),TRANSPOSE(COLUMN(Attendee)^0)),0))

Но это означает, что мне нужно ввести имя пользователя, прежде чем я смогу соответствовать компании

а также

=INDEX(Attendee,MATCH(0,COUNTIF($G$4:I4,list),0))

Дает мне имена пользователей в горизонтальном списке .... но мне нужно, чтобы список в вертикальном формате

ecompany name, user1, user2, user3, user4, user5, user6, user7
company1, bob, sue, dave, , ,john, mary
company2, dave, barry, steve, dan, , , pete

я хотел бы, чтобы данные отображались в двух столбцах для слияния

Company Name, User
company1, bob
company1, sue
company1, dave
company1, john
company1, mary
company2, dave
company2, dave
company2, barry
company2, steve
company2, dan
company2, pete

Я надеюсь это имеет смысл?

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

ВЫБОР ОБРАЗЦА:

Answer 1

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

Это предполагает, что у вас есть разумное (и известное) максимальное количество посетителей. Ваш скриншот показал 6.

Формула для левого верхнего угла диапазона ячеек назначения:

=IF(ISBLANK(L2),"",CONCATENATE($E2,", ",L2))

Эта формула копирует / перетаскивает, производя результаты, как показано.

Оттуда тривиально скопировать весь выходной блок данных (L8:Q12 в моем примере листа, как показано), вставить значения и вручную переместить выходные столбцы 2–6 под столбцом 1 (поэтому в моем примере листа выберите M8:M12, вырезать и вставьте в L13 и т. д.), затем отсортируйте в алфавитном порядке, чтобы вытолкнуть пустые данные, и у вас есть список для MM.

Answer 2

ХОРОШО. Это все еще «тупой» способ, но он требует только одного простого ручного шага и фактически соответствует спецификации, поскольку он представляет ваши результаты в двух столбцах по запросу.

Данные:

ШАГ 1:

Я определил именованные диапазоны КОМПАНИЙ F2:F99 и ИМЕНА L2:AB99

ШАГ 2:

Вот ваш вывод:

С формулой в столбце B:

=INDEX(NAMES,ROUNDUP((ROW()-1)/17,0),MOD(ROW()-2,17)+1)

Это просто формула для n-го столбца / каждых n строк, для которой n вручную установлено на 17 (вы можете легко изменить это жестко запрограммированное значение или заменить его какой-либо функцией подсчета или ссылкой на ячейку на функцию подсчета в вашей таблице данных и т.д. И т.д. по мере необходимости). Он должен начинаться во второй строке (как показано в примере), иначе необходимо будет корректировать смещения вызовов ROW() . Но помещенная на новый лист в ячейке B2 , эта формула скопирует и прочитает первые 17 столбцов первой строки именованного диапазона NAMES, затем первые 17 столбцов второй строки и т.д.

ШАГ 3:

Я написал A2 с полуобжаренной версией той же формулы, чтобы просто получить первый ряд из названного диапазона 17 раз, затем второй ряд и т.д.

=INDEX(COMPANIES,ROUNDUP((ROW()-1)/17,0))

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

ШАГ 4:

Вот глупая часть ... Возьмите свой выходной лист, выберите все, скопируйте, вставьте значения, отфильтруйте, удалите строки с 0 в столбце B, удалите фильтр, пересортируйте, Боб - ваш дядя.

ПОСЛЕСЛОВИЕ

Я уверен, что это можно сделать намного более аккуратно с помощью цикла for в VBA. Вы можете, вероятно, даже найти код, который быстро адаптируется к этому конкретному случаю использования, в других вопросах этого подпункта. Я УВЕРЕН, что это может быть сделано без шага 4 с хорошей формулой, написанной кем-то более опытным, чем я. К счастью, шаг 4 займет всего около 10 секунд, независимо от того, насколько велик ваш список участников, поэтому быстрые и грязные работы в этом случае.

Answer 3

Вы могли бы транспонировать?

Так что, если я правильно понимаю, когда вы вставляете значения, вставьте в горизонтальную линию.

Если вы скопируете их и в столбце вы хотите вставить их в специальную вставку и выбрать транспонирование, они будут помещены в этот столбец, но вставлены вертикально.