excel function excel-formula array-formulas dynamic-arrays

Combine Dynamic Arrays in Excel without VBA

I have two dynamic arrays in the same spreadsheet, called Input and Length:

Input =TRANSPOSE(BASE(DEC2BIN(CODE(MID(A1,(SEQUENCE(1,LEN(A1),,1)),1))),10,8))

Length =TRANSPOSE(MID(BASE((LEN(A1)*8),2,64),SEQUENCE(1,8,,8),8))

Input       Length
01010011    00000000
01101111    00000000
01101101    00000000
01100101    00000000
00100000    00000000
01100111    00000000
01110101    00000000
01101001    10110000
01100100    
01100001    
01101110    
01100011    
01100101    
00100000    
01101110    
01100101    
01100101    
01100100    
01100101    
01100100    
00100000    
00100000

I want to combine them into a single array:

The formula I am attempting to use is:

=INDEX((Input,Length),SEQUENCE(ROWS(Input)+ROWS(Length)))

However the Length range is generating a #REF! error, which I cannot resolve:

I have attempted to use other functions which either failed to combine the arrays, and/or generated various other #NUM! and #VALUE! errors.

I have also swapped the order from Input,Length to Length,Input, which generates the same #REF! error for the second array.

I suspect I have been looking at it too long and simply need fresh eyes.

I want to use native Excel functions and formulas instead of VBA or other programmatic approaches.

Solution

One way:

=TEXT(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",1,TRANSPOSE(IFNA(IF(SEQUENCE(,2,0),Length,Input),"")))&"</b></a>","//b"),REPT(0,8))

More rigorous alternative:

=LET(α,ROWS(Input),β,ROWS(Length),γ,MAX(α,β),δ,SEQUENCE(2*γ,,0),ε,INDEX(CHOOSE(SEQUENCE(,2),Input,Length),1+MOD(δ,γ),1+QUOTIENT(δ,γ)),FILTER(ε,1-ISNA(ε)))