excelexcel-formulaexcel-365

Remove 0's from a matrix

Hi I have the following data

December January February March April
0 0 0 0 116282
0 0 116282 116282 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
122207 0 0 0 0
0 122207 122207 0 0

And want the following output

Months Aging Numbers
December 122207
January 122207
February 116282, 122207
March 116282
April 116282

This data is currently being held in a dynamically generated range and has 2414 rows and 27 columns. Each value represents the cross section of case numbers that receive retro payments in a given month case number 122207 receives a retro payment in december, case number 116282 and 122207 both receive retro payment in February.

I generate the range through the following formula:

=LET(a, RetroTable[Case Number],
     b, RetroTable[Retro Units], 
     c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])), 
     a*ABS(b*c))

With A2# being a 27x1 array of months (2 years and 3 months of retro units tracking.

Basically, what happens is if there's a non-0 number in the retro units column of retro table and a non-zero number in the particular month column in the table, then it's considered an aging case, and I want to document it as such. Then multiplying it by the actual number and I get the actual case number.

But what I want is an array that has only the non-zero numbers. I have tried adding in a filter condition, such as:

=LET(a, RetroTable[Case Number],
     b, RetroTable[Retro Units], 
     c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])), 
     filter(a*ABS(b*c), a*ABS(b*c)<>0))

But this produces a #VALUE error

I have tried the small formula

=LET(a, RetroTable[Case Number],
     b, RetroTable[Retro Units], 
     c, CHOOSECOLS(RetroTable, XMATCH(Analysis!A2#, RetroTable[#Headers])), 
     SMALL(IF(a*ABS(b*c)=0, "", a*ABS(b*c)), SEQUENCE(ROWS(RetroTable))))

But as the data is needed on a monthly basis, this formula doesn't work because it loses the column/row integrity. It turns everything into a single column, and I need to keep each month separated but connected.

What I want is to remove all the 0s, collapse each monthly column into only its values, and have a variable list length for each month based on how many non zero numbers show up in the original report.

Solution

  • Try something along the lines using MAKEARRAY() :

    enter image description here

    =LET(
     _Body, RetroTable,
     _Rows, MAX(MMULT(SEQUENCE(,ROWS(_Body))^0,N(_Body>0))),
     _Output, IFERROR(MAKEARRAY(_Rows,COLUMNS(_Body),
     LAMBDA(r,c, INDEX(FILTER(INDEX(_Body,,c),INDEX(_Body,,c),""),r))),""),
     VSTACK(RetroTable[#Headers],_Output))

    Updated Formula as per expected output edit by OP:

    enter image description here

    =TRANSPOSE(VSTACK(RetroTable[#Headers],
 BYCOL(RetroTable,LAMBDA(α,TEXTJOIN(",",1,IF(α,α,""))))))