arraysgoogle-sheetsgoogle-sheets-formula

Embedding multiple arrays inside another array at various points according to a true/false condition


I'm trying to embed a few separate arrays into a larger array at points where a true/false condition is met. I have a list of "Basic Items" which form the base of the array. Based on a true or false on the same row as each "Basic Item" (SplitBoolean), I want to add the "Basic Item" to the array when it's false, and when it is true I want to pull an entire different, smaller range to add to the main array. I plan on pulling these smaller arrays (with "Split Items") into the main array by using JOIN and then splitting them later, but I can't get the main array to actually add the correct Split Item Array.

Here is what I mean as far as the base arrays and the desired output:

Basic Items Array: {"Item A";"Item B";"Item C";"Item D"} etc.

Split Items Array: {"Item B v.1";"Item B v.2"; "Item D v.1"} etc.

SPLIT_ITEM_ARRAY (*named function with a conditional range and basic item name as arguments. here would be "Item B"'s result): {"Item B v.1"; "Item B v.2"}

DESIRED OUTPUT WHEN ITEMS B AND D ARE TRUE: {"Item A"; "Item B v.1"; "Item B v.2"; "Item C"; "Item D v.1"}

Right now for the SPLIT_ITEM_ARRAY(monthrange,itemname) code I have =iferror(sort(filter(arrayformula(if(monthrange>0, SplitItemName,"")),if(iserror(arrayformula(find(itemname,SplitItemName))),false,true))),"") where "monthrange" is a range that has a number>0 if the split item is supposed to be included from the Split Items Array.

For actually making the array, I have tried a couple of things but none of them work. =arrayformula(if(SplitBoolean=false,BasicList,let(x,arrayformula(row(index(BasicList))),join(";",SPLIT_ITEM_ARRAY("jan",indirect(address(X,1))))))) ^ This one simply repeats all of the Item A splits in the spots where the Item B etc. splits are supposed to be.

=ARRAYFORMULA(if(SplitBoolean=false,BasicList,JOIN(";",SPLIT_ITEM_ARRAY("jan",index(BasicList,match(map(BasicList,lambda(val,indirect("sheet1!a"&val))),BasicList,0)))))) ^This one leaves blanks where the splits are supposed to be.

There are a few more example attempts in the sample spreadsheet I've attached here.

screenshot of sheet2 of the spreadsheet with the split items lists and month ranges

screenshot of sheet1 of the spreadsheet with the basic items list and some attempts to get the array to work

Note I would rather not use AppScript but am open to named functions. Thanks for any help you can give!


Solution

  • Here's a generalized approach which you may adapt accordingly:

    =reduce("Jan_",A2:A,lambda(a,c,vstack(a,if(c="",tocol(,1),if(index(B:B,row(c)),ifna(sort(filter(Sheet2!A:A&" v."&Sheet2!B:B,Sheet2!A:A=c,Sheet2!E:E)),c),c)))))
    

    enter image description here