Combine Dynamic Arrays in Excel without VBA

Question

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.

look here: stackoverflow.com/q/71022776/16578424. And be aware that you will reference the "input" range by referencing the cell where the formula is entered plus # --> A1# - you do not reference the whole range because this might change. — Ike
– Ike, Commented Mar 3, 2022 at 11:02
@Ike I understand the spill issue, which is why I used the SEQUENCE function to create a dynamic definition of the rows. I tried what I think you're suggesting: =INDEX((B2#,C2#),SEQUENCE(ROWS(B2#)+ROWS(C2#))), which still results in the #REF! error. — rayleone
– rayleone, Commented Mar 3, 2022 at 11:39

Jos Woolley · Accepted Answer · 2022-03-03 12:31:23Z

2

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

edited Mar 3, 2022 at 12:31

answered Mar 3, 2022 at 11:53

Jos Woolley

9,1322 gold badges7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rayleone Over a year ago

This works to a limit. When the Input array is generated from the maximum Excel cell capacity of 32,767 characters, this formula results in a #CALC! error.

Jos Woolley Over a year ago

Have added a more rigorous alternative.

rayleone Over a year ago

Holy sheet! More rigorous indeed @Jos! At this point I will confirm it works, although I have little idea on how. Clearly over my skis here. I need to do some additional downstream work on this, before I close it out. Thank you!

rayleone Over a year ago

Great stuff @Jos! I was even able to expand your formula further:

=LET(α,ROWS(B2#),β,ROWS(C2#),ç,ROWS(D2#),γ,MAX(α,β,ç),δ,SEQUENCE(3*γ,,0),ε,INDEX(CHOOSE(SEQUENCE(,3),B2#,C2#,D2#),1+MOD(δ,γ),1+QUOTIENT(δ,γ)),FILTER(ε,1-ISNA(ε)))

Jos Woolley Over a year ago

@konalion Nice work!

Collectives™ on Stack Overflow

Combine Dynamic Arrays in Excel without VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related