excelexcel-formulaexcel-lambda

Excel, Expand multiple ranges in one array formula

I have a workbook where I want to check for gaps in allocated ranges and I am struggling to build the ranges so that I can stack together the arrays. Here is a simplified statement of what I want to achieve:

Sample data:

Start End
1 3
5 7
9 10

What I want: An array within a single cell containing: { 1; 2; 3; 5; 6; 7; 9; 10 } which I can then process further in that same cell.

Here is an Excel formula for what I have tried so far:

=LET(rangeArray, {"1:3";"5:7";"9:10"},
   first, INDEX(rangeArray, 1, 1),
   firstExpanded, ROW(INDIRECT("" & first)),
   arrayExpanded_A, ROW(INDIRECT(rangeArray)),
   arrayExpanded_B, BYROW(rangeArray, LAMBDA(set, ROW(INDIRECT(set)))),
   rangeArray
)

In the above LET function rangeArray represents the sample and in my actual workbook I have already written the Excel functions to get me to that content. rangeArray may have one or many elements.

My plan is to use ROW(INDIRECT(...)) to expand each ranged element into a distinct array, which I can then use VSTACK to join them together to yield my objective.

Label Outputs My Analysis
rangeArray Spill function, showing the three initial array elements Note, there could be one or many elements in the array
first The first array element, as a non-array entity If INDEX(..) does not have he 1, 1 fields, an array entity is returned and the next function errors.
firstExpanded Spill of the values { 1; 2; 3 } Proof of concept. first must not be an array element.
arrayExpanded_A Spill of the values { 1; 5; 9 } INDIRECT() evaluates to INDIRECT({#VALUE!;#VALUE!;#VALUE!}) because INDIRECT cannot take array arguments.
arrayExpanded_B Spill of the values {"1:3";"5:7";"9:10"} The LAMBDA part of this returns #VALUE!, thus only the rangeArray is returned. Why?

In the arrayExpanded_B output, I think that the LAMBDA part is erroring because of a mismatch in the number of arguments, but I don't know how to fix that.

I can't help thinking that a LAMBDA function is the way to go, but I'm new to LAMBDA, and cannot see the solution yet. Any help gladly welcome.

Regards, Nigel

As described in the main body, I know that ROW(INDIRECT()) can expand out any single range. I am exploring BYROW() with LAMBDA functions, but these are returning errors. There may be other methods but I do not want to use VBA.

Solution

  • You would need a way to iterate the input one by one. This can be achieved through REDUCE(). Try:

    enter image description here

    Formula in A1:

    =DROP(REDUCE(0,{"1:3";"5:7";"9:10"},LAMBDA(x,y,VSTACK(x,LET(z,--TEXTBEFORE(y,":"),SEQUENCE(TEXTAFTER(y,":")-z+1,,z))))),1)

    Or, a volatile version:

    =DROP(REDUCE(0,{"1:3";"5:7";"9:10"},LAMBDA(x,y,VSTACK(x,ROW(INDIRECT(y))))),1)

    Note: I've only use {"1:3";"5:7";"9:10"} since that seems to be your starting input.