Search code examples
exceldynamicvstackhstack

Excel: Dynamic stacking or arrays n-number of times


Problem: Stacking arrays horizontally/vertically n-number of times

Examples:

  1. Horizontal array {"A", "B", "C"} stacked horizontally n=3 times = {"A", "B", "C", "A", "B", "C", "A", "B", "C"}
  2. Horizontal array {"A", "B", "C"} stacked vertically n=2 times = {"A", "B", "C"; "A", "B", "C"}

Undesirable solutions:

This problem is trivial manually by just providing the n-amount of arrays to a VSTACK() or HSTACK() -formula. A brute-force dynamic solution can be made using vector manipulation (e.g., TOCOL() and TRANSPOSE()) and MAKEARRAY() to create an index of the desired size and then INDEX(MATCH()) the values to the structure.

  1. Manual (horizontal): =HSTACK({"A", "B", "C"},{"A", "B", "C"},{"A", "B", "C"})
  2. Manual (vertical): =VSTACK({"A", "B", "C"},{"A", "B", "C"})
  3. Dynamic brute-force (horizontal): =LET(Array,{"A","B","C"},n,3,INDEX(Array,MATCH(TOROW(SEQUENCE(COLUMNS(Array))*MAKEARRAY(1,n,LAMBDA(r,c,1)),0,TRUE),SEQUENCE(COLUMNS(Array)),0)))
  4. Dynamic brute-force (vertical): =LET(Array,{"A","B","C"},n,2,INDEX(Array,MATCH(TRANSPOSE(SEQUENCE(COLUMNS(Array))*MAKEARRAY(1,n,LAMBDA(r,c,1))),SEQUENCE(COLUMNS(Array)),0)))

Desirable solution: Succinct formula solution(s) for horizontal/vertical stacking which take the array and n as inputs - possibly the direction too (i.e., horizontal or vertical).

Horizontally and vertically stacked arrays


Solution

  • Vertical stack 2 times:

    ={"A","B","C"}&EXPAND("",2,1,"")

    Horizontal stack 3 times:

    =TOROW({"A","B","C"}&EXPAND("",3,1,""))