google-sheetsgoogle-sheets-formularepeat

Repeat Columns of equation across rows infinitely

In Google Sheets, is there a way I can repeat columns of equations across rows laterally??

In [Pic1]1, I have a set of data from A1:C (in red color), listing out employee no., name and salary.

Below is the equations in yellow to show data in A10 =QUERY($A$2:$C$7,"Order by A Desc"), the equation in D10 is to multiple employee salary by 2 times =C10:C*2

Is there a way / any forumula I can type to keep on repeating A9:D set of equations across rows laterally like in [Pic2]2 (yellow)?

Manually copying and pasting across rows is very troublesome if copied in large amounts. It would be great is there is a formula way to achieve that.

Solution

  • To concat multiple arrays horizontally, we can use array literals {arr1,arr2}. To automate this process, we can use REDUCE to loop and accumulate the arr multiple times:

    =REDUCE(arr,SEQUENCE(number_of_times_to_repeat-1),LAMBDA(a,c,{a,arr}))

    Sample:

    To repeat A9:D three times,

    =REDUCE(A9:D,SEQUENCE(2),LAMBDA(a,c,{a,A9:D}))