How do I convert an Excel calculation into a spilled dynamic array

Question

I'm not even sure how to ask what I'm looking for, without describing the full question.

if I have this table (CSV form at the bottom if anyone wants it):

      A             B         C              D             E             F            G            H            I          J
| Date Performed | Hours | Pay Date    | Pay Period  | Pay Type   | Verification | Period 1 | Pay Type     | Period 2 | Pay Type
| 1-Oct-2021     | 1.5   | 24-Sep-2022 | 1           | base pay   | 1-May-2022   | 4.0      | base pay     | 4.0      | base pay
| 29-Oct-2021    | 1.5   | 24-Sep-2022 | 1           | retirement | 2-May-2022   | 2.5      | base pay     |          |
| 29-Nov-2021    | 1.5   | 24-Sep-2022 | 2           | retirement | 19-May-2022  | 4.0      | base pay     | 4.0      | base pay
| 23-Dec-2021    | 1     | 24-Sep-2022 | 1           | retirement | 22-Sep-2022  |          |              | 2.5      | retirement
| 23-Dec-2021    | 2     | 24-Sep-2022 | 2           | retirement | 23-Sep-2022  | 4.0      | retirement   | 4.0      | retirement
| 27-Jan-2022    | 0.5   | 24-Sep-2022 | 2           | retirement | 24-Sep-2022  | 4.0      | inconsistent | 4.0      | retirement
| 2-Feb-2022     | 0.5   | 23-Sep-2022 | 1           | retirement | 25-Sep-2022  |          |              | 4.0      | retirement
| 22-Feb-2022    | 2     | 23-Sep-2022 | 1           | retirement
| 27-Feb-2022    | 2.5   | 23-Sep-2022 | 2           | retirement
| 28-Feb-2022    | 0.5   | 23-Sep-2022 | 2           | retirement
| 2-Mar-2022     | 0.5   | 23-Sep-2022 | 1           | retirement
| 3-Mar-2022     | 1     | 23-Sep-2022 | 1           | retirement
| 4-Mar-2022     | 0.5   | 25-Sep-2022 | 2           | retirement
| 6-Mar-2022     | 1     | 23-Sep-2022 | 2           | retirement
| 6-Mar-2022     | 2     | 1-May-2022  | 1           | base pay
| 7-Mar-2022     | 2     | 1-May-2022  | 1           | base pay
| 8-Mar-2022     | 0.5   | 2-May-2022  | 1           | base pay
| 8-Mar-2022     | 1.5   | 25-Sep-2022 | 2           | retirement
| 9-Mar-2022     | 1     | 22-Sep-2022 | 2           | retirement
| 10-Mar-2022    | 1     | 1-May-2022  | 2           | base pay
| 13-Mar-2022    | 1.5   | 1-May-2022  | 2           | base pay
| 14-Mar-2022    | 1.5   | 1-May-2022  | 2           | base pay
| 15-Mar-2022    | 1     | 2-May-2022  | 1           | base pay
| 17-Mar-2022    | 1     | 2-May-2022  | 1           | base pay
| 17-Mar-2022    | 2     | 25-Sep-2022 | 2           | retirement
| 18-Mar-2022    | 1     | 22-Sep-2022 | 2           | retirement
| 24-May-2022    | 0.5   | 22-Sep-2022 | 2           | retirement
| 19-May-2022    | 4     | 19-May-2022 | 1           | base pay
| 19-May-2022    | 4     | 19-May-2022 | 2           | base pay

F2: =SORT(UNIQUE(FILTER(C:C,ISNUMBER(C:C))))

G2: =LET(x,F2#,SUMIFS(B:B,C:C,x,D:D,1))

I2: =LET(x,F2#,SUMIFS(B:B,C:C,x,D:D,2))

One complete pay period is 4hr; there can be up to two pay periods in a day. But, if there are only a handful of hours used, they can be incrementally combined into another arbitrary day, and get paid-out when a pay period aggregates to 4hr.

I get get H2 to work as: =IF(--G2=0,"",LET(x,FILTER(E:E,(C:C=F2)*(D:D=1)),IF(AND(EXACT(x,@x)),@x,"inconsistent")))

Essentially it ensures that everything in a given pay date/pay period is funded from the same pool, either the base pay pool or the retirement pool.

I can copy/paste that just fine. But, is there a way H2 can be re-formulated to yield a dynamic spilled range the same length as F2# and G2#?

(In case anyone is curious, this is how US Military Reserve pay works, this is a tracker I'm using to make sure I'm being honest in what I'm claiming.)

CSV form of the above table:

Date Performed,Hours,Pay Date,Pay Period,Pay Type,,Verification,Period 1,Pay Type,Period 2,Pay Type
1-Oct-2021,1.5,24-Sep-2022,1,base pay,1-May-2022,4.0,base pay,4.0,base pay
29-Oct-2021,1.5,24-Sep-2022,1,retirement,2-May-2022,2.5,base pay,,
29-Nov-2021,1.5,24-Sep-2022,2,retirement,19-May-2022,4.0,base pay,4.0,base pay
23-Dec-2021,1,24-Sep-2022,1,retirement,22-Sep-2022,,,2.5,retirement
23-Dec-2021,2,24-Sep-2022,2,retirement,23-Sep-2022,4.0,retirement,4.0,retirement
27-Jan-2022,0.5,24-Sep-2022,2,retirement,24-Sep-2022,4.0,inconsistent,4.0,retirement
2-Feb-2022,0.5,23-Sep-2022,1,retirement,25-Sep-2022,,,4.0,retirement
22-Feb-2022,2,23-Sep-2022,1,retirement,,,,,
27-Feb-2022,2.5,23-Sep-2022,2,retirement,,,,,
28-Feb-2022,0.5,23-Sep-2022,2,retirement,,,,,
2-Mar-2022,0.5,23-Sep-2022,1,retirement,,,,,
3-Mar-2022,1,23-Sep-2022,1,retirement,,,,,
4-Mar-2022,0.5,25-Sep-2022,2,retirement,,,,,
6-Mar-2022,1,23-Sep-2022,2,retirement,,,,,
6-Mar-2022,2,1-May-2022,1,base pay,,,,,
7-Mar-2022,2,1-May-2022,1,base pay,,,,,
8-Mar-2022,0.5,2-May-2022,1,base pay,,,,,
8-Mar-2022,1.5,25-Sep-2022,2,retirement,,,,,
9-Mar-2022,1,22-Sep-2022,2,retirement,,,,,
10-Mar-2022,1,1-May-2022,2,base pay,,,,,
13-Mar-2022,1.5,1-May-2022,2,base pay,,,,,
14-Mar-2022,1.5,1-May-2022,2,base pay,,,,,
15-Mar-2022,1,2-May-2022,1,base pay,,,,,
17-Mar-2022,1,2-May-2022,1,base pay,,,,,
17-Mar-2022,2,25-Sep-2022,2,retirement,,,,,
18-Mar-2022,1,22-Sep-2022,2,retirement,,,,,
24-May-2022,0.5,22-Sep-2022,2,retirement,,,,,
19-May-2022,4,19-May-2022,1,base pay,,,,,
19-May-2022,4,19-May-2022,2,base pay,,,,,

Answer 1

I'm not sure why there are still so many who continue to misunderstand which functions can be passed arbitrarily sized ranges (entire columns, for example) with no detriment to calculation performance. Just one instance of a construction such as

FILTER(E:E,(C:C=F2)*(D:D=1))

for example, is being forced to iterate over more than two million cells, since no implicit detection of the last-used cells within those ranges is employed by that function.

Try this set-up for Period 1:

=LET(ζ,MMULT(GESTEP(COUNTIFS(C2:C30,F2#,D2:D30,1,E2:E30,TRANSPOSE(UNIQUE(E2:E30))),1),SEQUENCE(2,,,0)),IF(ζ=0,"",IF(ζ=1,INDEX(E2:E30,MATCH(F2#&"|1",C2:C30&"|"&D2:D30,0)),"Inconsistent")))

keeping the ranges passed to a sensible length.

The part

"|1"

can be replaced with

"|2"

for the equivalent Period 2 set-up, adding an error clause to the above formula if desired (for May 2nd, for example, there is no Pay Period 2 entry).

Answer 2

If using functions is not a requirement for you, you can also use a pivot table.

Go to insert - pivot table - make sure "add to data model" is checked and click ok.

in Power Pivot tab - measures - insert measure and create one with this formula:
=if (DISTINCTCOUNT([Pay Type])=1,DISTINCT(Table1[Pay Type]),"inconsistent")