need to add 2 space after every data.

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
996
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDE
1ZYN33%38821%340
2ON!13%35010%324
3VELO8%33310%312
4ROGUE7%3276%305
5COPENHAGEN7%3719%322
6
7
8
9
10
11ZYN33%21%
12388340
13
14
15ON!13%10%
16350324
17
18
19VELO8%10%
208%10%
21
22
23ROGUE7%6%
24327305
25
26
Sheet1
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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?
 
Upvote 0
Try:
Book1
ABCDE
1ZYN33%38821%340
2ON!13%35010%324
3VELO8%33310%312
4ROGUE7%3276%305
5COPENHAGEN7%3719%322
6
7
8
9
10
11ZYN33%21%
12388340
13
14
15ON!13%10%
16350324
17
18
19VELO8%10%
20333312
21
22
23ROGUE7%6%
24327305
25
26
27COPENHAGEN7%9%
28371322
29
30
Sheet5
Cell Formulas
RangeFormula
A11:C30A11=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.
 
Last edited:
Upvote 0
Another possibility to handle a greater number of value columns:

Excel Formula:
=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:

Excel Formula:
=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)
    )
)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top