How to Use Excel Spill Range To Convert Multi-Column Array into Two Column Array

Question

I'm trying to create a spill-range solution to turn a list of dates and multiple columns of names into a structured two columns of data. I can do this using VBA, but because this will be automated and web-based, I need a spill-range solution.

A sample file of my situation can be found in this file.

As you'll see below, I have a list of a set of employees ("slackers") who have requested vacation/PTO during December. I've created a list of two spill range formulas that are:

1. A Column listing all days December
2. A list of slackers requesting the day off (array going horizontal).

Part 2 presents the problem as the the number of slackers is inconsistent and I'm not sure how to create additional dates for each row. Thus my problem is how to structure a filter/Array formula to list each employee, by day.

In the following tab, you can see my desired outcome. I've used a macro to generate this, but because this is web-based, I cannot use it.

I've tried a variety of mixing and matching array formulas and filters but cannot find a way to populate the date with each name.

While my question is scoped to addressing this conversion from multi-column to 2-column approach, I am happy to hear comments addressing the overall concept.

Answer 1

You may need to adjust maxWidth if there are more than 8 potential slackers.

=LET(maxWidth,8,
days,ByDay!A2#,
slackerBox,OFFSET(days,,1,,maxWidth),
ndx,SEQUENCE(ROWS(days)*maxWidth),
ndxDay,INT(ndx-1)/maxWidth+1,
slackerList,INDEX(slackerBox,ndxDay,MOD(ndx-1,maxWidth)+1),
FILTER(CHOOSE({1,2},INDEX(days,ndxDay),slackerList),slackerList<>0))

Answer 2

Here it is with your datatable:

=LET(
    end,       PtoRequestsTable[End],
    strt,      PtoRequestsTable[Start],
    us,        PtoRequestsTable[Slacker],
    usCnt,     COUNTA(us),
    lst,       DATE(YEAR(TODAY()),12,1),
    led,       DATE(YEAR(TODAY()),12,31),
    dtSq,      INT(SEQUENCE((led-lst+1)*usCnt,,lst,1/usCnt)),
    md,        MOD(SEQUENCE((led-lst+1)*usCnt,,0),usCnt)+1,
    ussl,      IF((INDEX(end,md)>=dtSq)*(INDEX(strt,md)<=dtSq),INDEX(us,md),""),
               SORT(FILTER(CHOOSE({1,2},dtSq,ussl),ussl<>""),1,-1)
)

It will automatically grow and shrink with the table. It also has the start and end dates as inputs.