Поместите список данных ячейки в одну ячейку на основе определенных значений

Question

Я знаю, что название вопроса не очень хорошее, я не могу придумать, как правильно сформулировать вопрос, и мне трудно найти ответы, просто ища в Интернете. У меня нет опыта в Excel, но я обычно могу понять, как сделать что-то с помощью пары быстрых поисков в Google.

До сих пор я пробовал несколько комбинаций функций TEXTJOIN(), FILTER(), SEARCH() and FIND() .

Я пытаюсь составить расписание на основе свободного времени.

Я использовал формы Google для опроса, когда люди свободны в какие дни (Лист 1).

Sheet 1 (Data)                         
    A      B         C
1  NAME   MON       TUES      
2  Dan    1, 2, 5   1, 4
3  Robert 12, 3     12, 3
4  Gregg  1, 2      4,
5  Joe    2, 3      3,

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

Sheet 2 (Results)
     A        B                  C   
1   TIME     MON                TUES
2    12      Robert             Robert 
3    1       Dan, Gregg         Dan
4    2       Dan, Gregg, Joe    
5    3       Robert, Joe        Robert, Joe 
6    4                          Dan, Gregg
7    5       Dan                

Answer 1

[Редактировать] добавлены скриншоты, альтернативная коррекция разграничения, дополнительная оговорка.

Это работает.

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ","")&","),),TRIM(Sheet1!$A:$A),""))

Введите в ячейку B2 затем Ctrl - Shift - Enter (CSE), чтобы сделать его формулой массива. Скопируйте и перетащите вниз.

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

• В любом месте между номерами все в порядке: 12, 1 ,2 .

Ярлык для сопоставления значений с разделителями в строке
Прежде чем функция поиска использует значения ячейки Sheet-2 (часовая ячейка) и Sheet-1 (часовая ячейка), эта формула удаляет пробелы и добавляет запятые как в правую, так и в левую часть ввода этих ячеек. Затем find ищет число в скобках в запятой в строке без пробелов; просто цифры и запятые с запятыми на обоих концах обоих аргументов.

• Find( ",2," , ",12,1,2," ) .
  Обратите внимание на запятые, заключающие в скобки оба конца поиска значения и поиска значения .

[Изменить] NB Эта формула не соответствует столбцам дня . Он использует относительные местоположения. Если порядок последовательности между данными Mon | Tue | Wed и результаты Mon | Wed отличается, тогда каждая формула столбца результата должна быть отредактирована, чтобы выровнять Sheet1!DayColumn ссылаются на DayColumn (или дополнительно настраивайте формулу для автоматического сопоставления столбцов по заголовкам).

Исправления разграничения

Приведенная выше формула объединяет числа, когда они разделены пробелом, и между ними нет запятой 1 2 → 12 .

Gregg добавляется к 12 в Вт, потому что пробел между 1 и 2 удаляется по формуле (Gregg не добавляется ни к 1 ни к 2). Джо был бы помещен во вторник в 14 часов, если бы это были 24ста часов (Джо не добавляется ни к 1 ни к 4).

Альтернативная коррекция очертаний. Замените пробелы запятыми 1 2 → 1,2 .

=TEXTJOIN(", ", TRUE, IF(IFERROR(FIND(","&TRIM($A2)&",",","&SUBSTITUTE(Sheet1!B:B," ",",")&","),),TRIM(Sheet1!$A:$A),""))

Грегг добавляется к 1 и 2 в вторник, потому что пробел между 1 и 2 заменяется запятой (Грегг не добавляется к 12). Джо добавляется в 1 и 4 .

Если ни одно из исправлений не является приемлемым, то создайте формулу целостности данных и:

• добавить формулу целостности в формулу результата
• поместите формулу целостности в столбец уведомлений
• используйте формулу целостности для условного форматирования (выделения) данных и / или результатов