Forum Discussion

BryanD55's avatar
BryanD55
Copper Contributor
Jul 06, 2024
Solved

Schedule View By Event

  I have a given excel schedule, similar to the "Input" table shown in the attachment.   I have a need to organize this table by event and show participants in individual columns.  I have this table...
excel
  • djclements's avatar
    djclements
    Jul 06, 2024

    BryanD55 One possible dynamic array solution for MS365 could be:

     

    =LET(
    input, A3:I10,
    dates, DROP(TAKE(input, 1),, 1),
    times, DROP(CHOOSEROWS(input, 2),, 1),
    events, DROP(input, 2, 1),
    test, events <> "",
    names, TOCOL(IFS(test, TAKE(DROP(input, 2),, 1)), 2),
    keyId, TOCOL(IFS(test, events & "|" & dates + times), 2),
    ids, SORT(UNIQUE(keyId)),
    recordCount, MAP(ids, LAMBDA(id, SUM(--(id = keyId)))),
    maxCount, MAX(recordCount),
    newRows, maxCount - recordCount,
    cols, SEQUENCE(, maxCount),
    pad_ids, TOCOL(IFS(newRows >= cols, ids), 2),
    resize, WRAPROWS(SORTBY(EXPAND(names, ROWS(keyId) + ROWS(pad_ids),, ""), VSTACK(keyId, pad_ids)), maxCount),
    output, HSTACK(TEXTBEFORE(ids, "|"), INT(TEXTAFTER(ids, "|")), MOD(TEXTAFTER(ids, "|"), 1), resize),
    headers, HSTACK("Event", "Date", "Time", "Participant " & cols),
    VSTACK(headers, output)
)

     

    Adjust the input range as needed. See attached...

  • BryanD55's avatar
    BryanD55
    Copper Contributor
    Jul 06, 2024

    HansVogelaar 

     

    Thanks for letting me know.  I edited permissions for anyone to view it.  I am putting a screenshot below in case it still doesn't work.

     

    https://1drv.ms/x/c/a511440084e0d8e5/EQZ30ON2VIFPsmBYSy9-8lkByCDOqEaD16Ilmmb-oOxE2A?e=BKymEc

     

     

     

     

    • djclements's avatar
      djclements
      Silver Contributor
      Jul 06, 2024

      BryanD55 One possible dynamic array solution for MS365 could be:

       

      =LET(
    input, A3:I10,
    dates, DROP(TAKE(input, 1),, 1),
    times, DROP(CHOOSEROWS(input, 2),, 1),
    events, DROP(input, 2, 1),
    test, events <> "",
    names, TOCOL(IFS(test, TAKE(DROP(input, 2),, 1)), 2),
    keyId, TOCOL(IFS(test, events & "|" & dates + times), 2),
    ids, SORT(UNIQUE(keyId)),
    recordCount, MAP(ids, LAMBDA(id, SUM(--(id = keyId)))),
    maxCount, MAX(recordCount),
    newRows, maxCount - recordCount,
    cols, SEQUENCE(, maxCount),
    pad_ids, TOCOL(IFS(newRows >= cols, ids), 2),
    resize, WRAPROWS(SORTBY(EXPAND(names, ROWS(keyId) + ROWS(pad_ids),, ""), VSTACK(keyId, pad_ids)), maxCount),
    output, HSTACK(TEXTBEFORE(ids, "|"), INT(TEXTAFTER(ids, "|")), MOD(TEXTAFTER(ids, "|"), 1), resize),
    headers, HSTACK("Event", "Date", "Time", "Participant " & cols),
    VSTACK(headers, output)
)

       

      Adjust the input range as needed. See attached...