Array Transformation functions

AVCLEAN

Reference

» Reference, Discussion, & Example Applications:

About

Array Vertical Clean, replaces errors with null strings and filters all the columns depending on nr. of blnks/null strings on each row.

Inputs:

  • ar : array
  • [n] : = 0 or omitted, filters only “full” rows sum(blnks) = 0
  • [n] : > 0 filters the rows that have sum(blnks) <= n
  • [n] : < 0 filters only rows that have sum(blnks) >= abs(n)

Code

M.S. Excel 
AVCLEAN = LAMBDA(ar, [n],
    LET(
        a, IF(ISERROR(ar), "", IF(ar = "", "", ar)),
        x, BYCOL(a, LAMBDA(a, SUM(--(a = "")))),
        m, MIN(ABS(n), MAX(x)),
        FILTER(a, IF(n >= 0, x <= m, x >= m))
    )
);