0

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.

What I have

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.

Desired Outcome

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Please note, if you're upset by your name appearing as a deadbeat, it was taken from the top SO VBA contributors list for the last 30 days (except for me). Suffice to say such folks are not deadbeats... I just wanted some random data and I call all my friends or people I admire deadbeats. – pgSystemTester Mar 23 '21 at 20:16
  • 3
    What if you're upset your name wasn't on the deadbeat list? Asking for a friend.... – BigBen Mar 23 '21 at 20:59
  • 2
    And really, you used the top user on vba when you can't use vba? probably should have used the top users on Excel-Formula. Also just responding for a friend. :) – Scott Craner Mar 23 '21 at 21:17
  • OH Jeez... these are tough comments... @ben... that may be the funniest thing you've ever posted, but seriously, why aren't you on there? – pgSystemTester Mar 23 '21 at 22:31
  • 1
    And @ScottCraner, I didn't even think of that... but that's incredibly insightful. That's like me working for a company named the word intelligence, but spelled wrong (oh wait... thta's real!) – pgSystemTester Mar 23 '21 at 22:31
  • "asking for a friend" @BigBen (Stil LOL) – pgSystemTester Mar 23 '21 at 22:33
  • @PGSystemTester - not on the list because 1) work is busy and 2) Python is awesome. – BigBen Mar 23 '21 at 22:34
  • @BigBen I know very little about Python beyond basic syntax. I actually made a comment in the mathematics section today trying to get more knowledge on it... https://math.stackexchange.com/questions/2845598/how-to-calculate-binomial-distribution-of-really-small am I missing out on something? – pgSystemTester Mar 23 '21 at 23:02
  • 1
    @PGSystemTester glad you hear you got a kick out of the comment. Humor always helps! – BigBen Mar 23 '21 at 23:47
  • This sounds like a job for an sql server – Braiam Mar 24 '21 at 19:33
  • @Braiam SQL might be a better fit from a technical perspective, but this only a few hundred records. – pgSystemTester Mar 29 '21 at 22:30

2 Answers2

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.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This answer is great! I'm upvoting, but going to accept the other only because the post author is less than 1k and you sir have already made it! – pgSystemTester Mar 23 '21 at 22:58
  • 1
    @PGSystemTester I answered because it was a puzzle, do not care about the points. One thing, the other uses OFFSET which is volatile and will recalc every time Excel recalcs. Just an observance. It also uses your intermediate data, while mine uses the source. – Scott Craner Mar 23 '21 at 23:01
  • 2
    I actually calculated all of what you mentioned. This means, yes I know your answer is better because answer is not volatile and its dynamica. Also that you don't care about points (hence new poster gets it). As I explain to people at work, excel problems are like crossword puzzles to me... not work, not fun... just something to do. Thanks again. You're still #1! – pgSystemTester Mar 23 '21 at 23:06
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))
Axuary
  • 1,497
  • 1
  • 4
  • 20