SOLVED

# Schedule View By Event

Copper Contributor

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

7 Replies

# Re: Schedule View By Event

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

# Re: Schedule View By Event

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.

Excel Schedule Help.xlsx

# Re: Schedule View By Event

@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.
best response confirmed by BryanD55 (Copper Contributor)
Solution

# Re: Schedule View By Event

@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),
output, HSTACK(TEXTBEFORE(ids, "|"), INT(TEXTAFTER(ids, "|")), MOD(TEXTAFTER(ids, "|"), 1), resize),
headers, HSTACK("Event", "Date", "Time", "Participant " & cols),
)``````

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

# Re: Schedule View By Event

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;
select 数量,replace(属性,'--','</td><td>'),group_concat(f01,'</td><td>') from Sheet1un_title_one_dim_first_row group by 数量,属性;

# Re: Schedule View By Event

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.

1 best response

Accepted Solutions
best response confirmed by BryanD55 (Copper Contributor)
Solution

# Re: Schedule View By Event

@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),