Insert blank rows

[alexlyle]

I know there are alot of discussions along these lines but I can't find one that fits my predicament. I have roughly 68K rows. I need to insert blank rows such that every 7th row is a blank row containing 118 blank spaces. so it would be:

Row1

Row2

Row3

Row4

Row5

Row6

Blank row of 118 spaces

Row8

Row9

Row10

Row11

Row12

Row 13

Blank row of 118 spaces

Row15

...

[fmvizcaino]

Hi @alexlyle ,

You can use generate rows + formula to achieve that.

Example attached as well.

Best,

Fernando Vizcaino

[Aaron_Harter]

Hi @alexlyle, 

You may be able to use the attached solution to split off a new record after each group of 6 from your source data, then use a Formula tool to hard code 118 spaces into the data set:

 

[fmvizcaino]

@alexlyle ,

Reuploading as I noticed you don't need the multiples by 7 included.

[alexlyle]

That is very helpful. Thanks. Any suggestions on how I can make the new rows have 118 blank spaces? Thanks

[fmvizcaino]

Hi @alexlyle ,

Instead of having a column as a number, if you format as a text, it is possible to fill as blank.

Example attached.

Best,

Fernando Vizcaino

[alexlyle]

what I need is a formula to specify 118 blank spaces. I'm trying to avoid having to type in 118 blank spaces.

Update- the leftpad formula is what i needed.

[alexlyle]

The final step is to put them back in the original format:

go from

to

I assume it involves using transpose but that doesn't work because it wants to aggregate. I don't want to combine anything. I just want to stack the values as separate columns under 1,2,3,4,5,6,7.

[Aaron_Harter · Accepted solution]

Hi @alexlyle, 

I've dropped in some new logic to help you with grouping & cross tab into your desired output format, where the 7th field contains 118 blank spaces:

 

[alexlyle]

A follow-up question. I need my process flow to add blank rows so that the total number of row is a multiple of 7. Ex, I have 82469 rows now. I need to add 5 rows to get 82474 and would like to follow the pattern below with the "mod" field. I'm thinking I would configure generate rows but not sure how.