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
Note I would rather not use AppScript but am open to named functions. Thanks for any help you can give!
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)))))