SOLVED

Schedule View By Event

Copper Contributor

 

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.

 

Sheet 

7 Replies

@BryanD55 

I'm afraid your link doesn't work:

HansVogelaar_0-1720301021275.png

@BryanD55 

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

schedule by event.png

 

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.

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

 

Excel Schedule Help.xlsx

 

BryanD55_0-1720306800651.png

 

 

 

@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

@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 

 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 数量,属性;

 

 

Screenshot_2024-07-07-14-08-42-896_com.mmbox.xbrowser.pro.jpg

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

1 best response

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

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

View solution in original post