Filter 2D array excluding blank rows and columns-CodePudding

Please, I need help with this. I have a 2D array or range of cells and I intend to use an excel array formula to filter this array such that all rows and columns containing entirely zero or empty cells are not included in the result. For example, in the image below, I want to filter the given data such that the data in Column C and Row 5 are removed because they contain entirely zero. Is it possible to achieve this within excel? If yes, which excel formula can I use? Note: I'm using Excel 2021.

CodePudding user response：

Using MMULT:

=LET(
    rng,A2:F6,
    clm,COLUMNS(rng),
    f,FILTER(rng,MMULT(--(--rng=0),SEQUENCE(clm,,1,0))<>clm),
    FILTER(f,TRANSPOSE(MMULT(--TRANSPOSE(f=0),SEQUENCE(ROWS(f),,1,0)))<>ROWS(f)))

All these should be available in Excel 2021

CodePudding user response：

You could do something like this:

=FILTER(FILTER(A2:F6,BYROW(A2:F6,LAMBDA(a,COUNTIF(a,">0")>0))),BYCOL(A2:F6,LAMBDA(c,COUNTIF(c,">0")>0)))

CodePudding user response：

You can use the following approach which uses CHOOSEROWS, CHOOSECOLS if you have such excel functions available in your Excel version. In cell G2:

=LET(rng, A1:E5, ri, ROW(A1), ci, COLUMN(A1),
  rows, BYROW(rng, LAMBDA(a, IF(COUNTIF(a,"<>0")>0, ROW(a)-ri 1,0))),
  cols, BYCOL(rng, LAMBDA(a, IF(COUNTIF(a,"<>0")>0, COLUMN(a)-ci 1,0))),
  CHOOSECOLS(CHOOSEROWS(rng, rows), cols)
)

or without LET function (shorter but more difficult to understand):

=CHOOSECOLS(CHOOSEROWS(A1:E5, BYROW(A1:E5, LAMBDA(a, IF(COUNTIF(a,"<>0")>0, 
  ROW(a)-ROW(A1) 1,0)))), BYCOL(A1:E5, LAMBDA(a, IF(COUNTIF(a,"<>0")>0, 
  COLUMN(a)-COLUMN(A1) 1,0))))

Here is the output

The main idea is to identify non-empty rows/columns in the range. We use BYROW/BYCOL respectively. It is important to assign 0 (non valid row or column) when the row/column is empty. CHOOSEROWS, CHOOSECOLS only take into account valid rows or columns values. Since we use ROW/COLUMN functions, in case the input range (rng) doesn't start at first row/column, we use ri, ci values to adjust it. If that is not the case it can be removed.