Dragging many independent formulas at once in Google Sheets

Question

I have a Sheet named Sheet1 like:

No.	1st Part	2nd Part	data1st	data2nd	title	desc	meta1	meta2	note
1	b	c	d	e	f	g	h	i	j
2	l	m	n	o	p	q	r	s	t

I want to generate another sheet based in this data, reordeing its data as:

-	-	-
b	c	f
merged with (b)	merged with (c)	h
merged with (b)	merged with (c)	i
merged with (b)	merged with (c)	j
d	e	merged with (j)
g	merged with (g)	merged with (j)

How would it be possible to set up these formulas so I can select all that rectangle, and drag it until the end of the sheet to generate the same rectangle for the second row in "Sheet1", etc.

Expected output:

-	-	-
b	c	f
merged with (b)	merged with (c)	h
merged with (b)	merged with (c)	i
merged with (b)	merged with (c)	j
d	e	merged with (j)
g	merged with (g)	merged with (j)
l	m	p
merged with (l)	merged with (m)	r
merged with (l)	merged with (m)	s
merged with (l)	merged with (m)	t
n	o	merged with (t)
q	merged with (q)	merged with (t)

Answer 1

Copy Cell Formats

Cells can't be merged using a function however you can set up the formatting in advance by creating the merged cells for one 6x3 block of cells, then pasting that formatting over subsequent blocks.

1. Setup the merge cell layout for the first 6 x 3 block of cells
   • Merge the necessary cells.
   • Set the horizontal and vertical alignment, fonts etc.
2. Select and Copy the block of cells.
3. Select the entire sheet by clicking on the tile in the top-left corner between the row 1 heading and the column A heading or CTRL+A
4. Paste using Paste special > Format only

Formula

The following formula will return the values for all blocks of cells. Place it in the top left cell of the first block.

=WRAPROWS(TOROW(BYROW(
    QUERY(Sheet1!B2:J, "WHERE B IS NOT NULL"), 
       LAMBDA(r, 
         MAP(HSTACK(1,2,5,,,7,,,8,,,9,3,4,,6,,),
           LAMBDA(i, IF(i, INDEX(r,,i),)))))),3)

BYROW  HSTACK  IF  INDEX  LAMBDA  MAP  QUERY  TOROW  WRAPROWS

Explanation

1. QUERY is used to remove empty rows from the range.
2. BYROW passes the results from QUERY, row-by-row into a LAMBDA function that stores the current row in r.
3. A 1x18 array is created using r's column index numbers in their final order in a flattened version of the 6x3 block of cells.
4. That array of indexes and empty values is mapped, value by value, into another LAMBDA function that stores the current index/value in i.
5. If i contains a number, the LAMBDA's formula uses i as the column argument for INDEX to retrieve the correct column from r.
6. This is repeated for each i, and then repeated for the next r until all r have been processed. The result is an array that has as many rows as there were populated rows in the original range and 18 columns.
7. TOROW transforms that array into a single row, and then WRAPROWS transforms it into an array that is 3 columns WIDE matching the final layout.