need to add 2 space after every data.

[sksanjeev786]

Hi Team

Can i get 2 spaces afer the stubs? shown in the below example

output from Row 11 to Row 24 and input are row 1 to 5

book1
	A	B	C	D	E
1	ZYN	33%	388	21%	340
2	ON!	13%	350	10%	324
3	VELO	8%	333	10%	312
4	ROGUE	7%	327	6%	305
5	COPENHAGEN	7%	371	9%	322
6
7
8
9
10
11	ZYN	33%	21%
12		388	340
13
14
15	ON!	13%	10%
16		350	324
17
18
19	VELO	8%	10%
20		8%	10%
21
22
23	ROGUE	7%	6%
24		327	305
25
26
Sheet1

 

[Joe4]

Are you looking to update the data in place, or always have a copy of it below the original data, like you have shown?
If below the original data, will there always be 5 rows of data, and you want to start at row 11?

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1	ZYN	33%	388	21%	340
2	ON!	13%	350	10%	324
3	VELO	8%	333	10%	312
4	ROGUE	7%	327	6%	305
5	COPENHAGEN	7%	371	9%	322
6
7
8
9
10
11	ZYN	33%	21%
12		388	340
13
14
15	ON!	13%	10%
16		350	324
17
18
19	VELO	8%	10%
20		333	312
21
22
23	ROGUE	7%	6%
24		327	305
25
26
27	COPENHAGEN	7%	9%
28		371	322
29
30
Sheet5

Cell Formulas
Range	Formula
A11:C30	A11	=LET( a,A1:A5, r,ROWS(a), ind,ROUNDUP(SEQUENCE(r*4)/4,0), name,IF(MOD(ROUNDUP(SEQUENCE(r*4),0),4)=1,INDEX(a,ind),""), HSTACK(name,TOCOL(EXPAND(B1:C5,r,4,"")),TOCOL(EXPAND(D1:E5,r,4,""))) )
Dynamic array formulas.

 

[djclements]

Another possibility to handle a greater number of value columns:

=LET(
    row_labels, A1:A5,
    values, B1:G5,
    val_fields, 2,
    blank_rows, 2,
    w, COLUMNS(values),
    n, w/val_fields,
    HSTACK(
        TOCOL(EXPAND(row_labels,,val_fields+blank_rows,"")),
        WRAPROWS(TOCOL(EXPAND(CHOOSECOLS(values,TOROW(SEQUENCE(n,val_fields),,1)),,w+n*blank_rows,"")),n)
    )
)

And to handle an uneven number of value columns, based on the number of value fields specified:

=LET(
    row_labels, A1:A5,
    values, B1:F5,
    val_fields, 2,
    blank_rows, 2,
    w, CEILING.MATH(COLUMNS(values),val_fields),
    n, w/val_fields,
    HSTACK(
        TOCOL(EXPAND(row_labels,,val_fields+blank_rows,"")),
        WRAPROWS(TOCOL(EXPAND(CHOOSECOLS(EXPAND(values,,w,""),TOROW(SEQUENCE(n,val_fields),,1)),,w+n*blank_rows,"")),n)
    )
)

 

[sksanjeev786]

Are you looking to update the data in place, or always have a copy of it below the original data, like you have shown?
If below the original data, will there always be 5 rows of data, and you want to start at row 11?

Hi Joe,

Sorry i missed this Thread from my list.

yes I need all the data after my original data. and my oringal data range can be anything but after 5 or 6 row gap I need data as mentioned in the row no 11

 

[sksanjeev786]

Try:

Book1
	A	B	C	D	E
1	ZYN	33%	388	21%	340
2	ON!	13%	350	10%	324
3	VELO	8%	333	10%	312
4	ROGUE	7%	327	6%	305
5	COPENHAGEN	7%	371	9%	322
6
7
8
9
10
11	ZYN	33%	21%
12		388	340
13
14
15	ON!	13%	10%
16		350	324
17
18
19	VELO	8%	10%
20		333	312
21
22
23	ROGUE	7%	6%
24		327	305
25
26
27	COPENHAGEN	7%	9%
28		371	322
29
30
Sheet5

Cell Formulas
Range	Formula
A11:C30	A11	=LET( a,A1:A5, r,ROWS(a), ind,ROUNDUP(SEQUENCE(r*4)/4,0), name,IF(MOD(ROUNDUP(SEQUENCE(r*4),0),4)=1,INDEX(a,ind),""), HSTACK(name,TOCOL(EXPAND(B1:C5,r,4,"")),TOCOL(EXPAND(D1:E5,r,4,""))) )
Dynamic array formulas.

Thank you so much for your support on this

it is working perfect

 

[sksanjeev786]

Another possibility to handle a greater number of value columns:

=LET(
    row_labels, A1:A5,
    values, B1:G5,
    val_fields, 2,
    blank_rows, 2,
    w, COLUMNS(values),
    n, w/val_fields,
    HSTACK(
        TOCOL(EXPAND(row_labels,,val_fields+blank_rows,"")),
        WRAPROWS(TOCOL(EXPAND(CHOOSECOLS(values,TOROW(SEQUENCE(n,val_fields),,1)),,w+n*blank_rows,"")),n)
    )
)

And to handle an uneven number of value columns, based on the number of value fields specified:

=LET(
    row_labels, A1:A5,
    values, B1:F5,
    val_fields, 2,
    blank_rows, 2,
    w, CEILING.MATH(COLUMNS(values),val_fields),
    n, w/val_fields,
    HSTACK(
        TOCOL(EXPAND(row_labels,,val_fields+blank_rows,"")),
        WRAPROWS(TOCOL(EXPAND(CHOOSECOLS(EXPAND(values,,w,""),TOROW(SEQUENCE(n,val_fields),,1)),,w+n*blank_rows,"")),n)
    )
)

Thank you so much for your support on this

it is working perfectly