[SOLVED] Combine Dynamic Arrays in Excel without VBA

Issue

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:

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

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:

01010011
01101111
01101101
01100101
00100000
01100111
01110101
01101001
01100100    
01100001    
01101110    
01100011    
01100101    
00100000    
01101110    
01100101    
01100101    
01100100    
01100101    
01100100    
00100000    
00100000
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!
#REF!

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(ε)))

Answered By – Jos Woolley

Answer Checked By – Candace Johnson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.