Forum Discussion

deanstanberry's avatar
deanstanberry
Copper Contributor
Aug 02, 2024

Dynamic array and spilled array - FILTER function behavior

I am attempting to create a dynamic dependent data validation list (using instructions found on the My Online Training YouTube video).  A link to the file is found here.

 

On the Standards Categories TAB, there is a Table (StdCategories) with two columns for Category and Subcategory.  The primary data validation list is for the Category values, and the dependent data validation is for the Subcategory values.

 

Starting in Cell E2, the Categories are populated horizontally using the UNIQUE and TRANSPOSE functions.  Starting in Cell E2, the FILTER and SORT functions are used to populate the Subcategory values for each Category.  There is some sort of error occurring in every other Column (starting in column F) where the work "None" is used as the error return value.  For some reason, the formula is not matching the value in Row 2 with the associated values in the StdCategories table.  I've verified this using the Evaluate Formula feature.  I've tried searching for any help on this issue, but I either can't find it, or I'm not searching with the right terminology.

 

Is anyone familiar with this odd behavior, or have any suggestions on diagnosing the error?

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Aug 02, 2024

    deanstanberry 

    If you keep your lists of categories and subcategories sorted, you can pull this off without the need to spill.

     

    Set up named items inside the table for category and subcategory then use this in data validation:

    =LET(
    first, XMATCH($A2, category),
    last, XMATCH($A2, category, , -1),
    r, last - first + 1,
    TAKE(DROP(subcategory, first - 1), r)
)

    Data validation doesn't like dynamic arrays but will allow TAKE/DROP.

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Aug 02, 2024

    deanstanberry 

    If you keep your lists of categories and subcategories sorted, you can pull this off without the need to spill.

     

    Set up named items inside the table for category and subcategory then use this in data validation:

    =LET(
    first, XMATCH($A2, category),
    last, XMATCH($A2, category, , -1),
    r, last - first + 1,
    TAKE(DROP(subcategory, first - 1), r)
)

    Data validation doesn't like dynamic arrays but will allow TAKE/DROP.

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 02, 2024

    deanstanberry 

    If you are willing to accept wrapped lists rather than a proper array of columns

    = LET(
    lists, GROUPBY(category, subcategory, 
           LAMBDA(v, TEXTJOIN(CHAR(10), , v)), , 0), 
    TRANSPOSE(lists)
)

    could give a reasonable effect.

    To do 'a proper job' one contend with the MS 'array of arrays' error.  Provided the number of columns is not too large

    = LET(
    distinct, TOROW(SORT(UNIQUE(category))),
    body, DROP(REDUCE("", distinct, 
          LAMBDA(tbl, hdr, HSTACK(tbl, FILTER(subcategory, category = hdr)))), , 1),
    VSTACK(distinct, IFERROR(body, ""))
 )

    would give a 2D array solution