Forum Discussion
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 shown as "Desired Output" in the attachment I also have Power BI available to me, if that provides an easier solution.
Any and all help would be greatly appreciated, thank you.
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...
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 数量,属性;=IFNA(DROP(REDUCE("",UNIQUE(A2:A16),LAMBDA(u,v,HSTACK(u,VSTACK(v,FILTER(B2:B16,A2:A16=v))))),,1),"")
With Office 365 or Excel for the web you can apply this formula.
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.
- 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.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.
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.
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...
