Search code examples
excelexcel-formulaexcel-365

Is there an Excel formula that produces a dynamic spill array that displays the sorted and filtered dates from a Table?

I have a Table with columns USER, CATEGORY, POINTS, and DATE called main_data. There may be duplicate values in any of the columns, and may be rows that are entirely identical to each other.

USER CATEGORY POINTS DATE
JDOE Cat1 0.5 7/23/2023
JDOE Cat1 0.5 11/23/2022
JDOE Cat3 1 5/20/2023
JSCHMO Cat2 0.5 7/23/2023
TEST Cat2 0.5 7/23/2023
JSCHMO Cat4 1 1/14/2023
JSCHMO Cat4 3 7/23/2023
JSCHMO Cat2 0.5 7/23/2023
TEST Cat2 0.5 1/1/2022
TEST Cat4 7 1/1/2023

I have a formula to display the total points for each user for each category (cell B1 in the table below):

=SUMIFS(main_data[POINTS], main_data[USER], A2#, main_data[CATEGORY], B1#, main_data[DATE], ">" & TODAY() - 365)

where A2 contains a spill range for USERs and B1 contains a spill range for CATEGORYs.

I'd like to have a formula that will spill downward and show a sorted list of the dates for the USER in that row. So row 2 would show all the dates in descending order for the USER in cell A2, row 3 would show for USER in A3, etc.

A B C D E F G H I
1 USER Cat1 Cat2 Cat3 Cat4
2 JDOE 1 0 1 0 11/23/2022 5/20/2023 7/23/2023
3 JSCHMO 0 1 0 4 1/14/2023 7/23/2023 7/23/2023 7/23/2023
4 TUSER 0 0.5 0 7 1/1/2023 7/23/2023

I can do this with a manually drag copied formula:

=TRANSPOSE( SORT( FILTER(main_data[DATE], ( (TODAY()-main_data[DATE]) < 365 ) * ( main_data[USER] = A2 ) ),, 1 ) )

and I can produce an array that has each row in main_data with the date broken out into a column based on USER:

=IF(TRANSPOSE(main_data[USER]) = UNIQUE(main_data[USER]), TRANSPOSE(main_data[DATE])*SEQUENCE(ROWS(UNIQUE(main_data[USER])),1,1,0),"")

but I don't know how to condense that down. If I could sort each row individually that would work, but my version of Excel doesn't have the latest round of updated formulas, so I can't use BYROW, LAMBDA, TEXTSPLIT, HSTACK, VSTACK, TOROW, REDUCE, etc. I have Version 2202 Build 14931.21040

Without condensation (and sorting) the number of columns will expand as the number of USERs does, and it will be full of blank cells.

Is it possible to do it with a dynamic array so that I wouldn't have to periodically drag-copy the formula downward as the number of USERs expands? I cannot use VBA as the workbook has to be stored on Sharepoint which does not allow VBA to run in online mode.

Solution 
  • =LET(a,SEQUENCE(ROWS(UNIQUE(main_data[USER]))),
     b,IF(TRANSPOSE(main_data[USER])=UNIQUE(main_data[USER]),TRANSPOSE(main_data[DATE])*a^0,""),
     c,SEQUENCE(ROWS(b)),
     s,SORT(b,c,SIGN(c),1),
     m,MMULT(--(TRANSPOSE(main_data[USER])=UNIQUE(main_data[USER])),ROW(main_data[USER])^0),
     r,SIGN(SEQUENCE(ROWS(s),COLUMNS(s)))+SEQUENCE(rows(s),,0),
     mc,MMULT((TRANSPOSE(m)+(SIGN(m)-1))*(SEQUENCE(,ROWS(m))>=SEQUENCE(ROWS(m))),SEQUENCE(ROWS(m),,,0)),
     i,MOD(SEQUENCE(rows(s),columns(s),columns(s))-mc,columns(s))+1,
INDEX(s,SEQUENCE(rows(s)),i))

    I hope this formula contains only formula's you have present in your version. Without REDUCE this is quite challenging.

    This formula uses your outcome b of dates matching the user ID's and sorts them s.

    this results in a cascaded spill, where the first row is sorted from the beginning, the second row of the spill starts where the previous row ended etc.: enter image description here

    m checks the number of dates per ID, row wise: enter image description here

    mc is to check the number of dates greater than the smallest date for it's ID. enter image description here

    these are used to create a sequence i to index the start column at the position of the first date of it's id to be used when indexing the sorted array s:

    enter image description here

    It may not be elegant, but it works.