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...

peiyezhu's avatar
peiyezhu
Bronze Contributor
Jul 06, 2024

BryanD55 

 online sql:

//select * from Sheet1 limit 20;
cli_unstack_title~Sheet1~1;

cli_one_dim_first_row~Sheet1un_title~1;
//select * from Sheet1un_title_one_dim_first_row;
cli_no_header;
select 数量,replace(属性,'--','</td><td>'),group_concat(f01,'</td><td>') from Sheet1un_title_one_dim_first_row group by 数量,属性;