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
OliverScheurich

Thanks for the reply! I actually trying to go from your point B to point A. I am starting with a table similar to your result and trying to use formulas to get your input.

I believe I fixed the link I posted to be viewable by anyone.
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 07, 2024

BryanD55 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

 

The data layout in the attached file is for illustration. You can place the green result table below the blue table or in another worksheet.