We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Copy data in adjacent rows, then paste into spaced rows (with X number of empty rows between)

asked 2021-02-06 00:39:32 +0200

appreciatethehelp gravatar image

updated 2021-02-06 05:56:54 +0200

Is there a way to Copy data that is oriented in a contiguous set of rows, as such:

1
2
3
4

And Paste it (without having to manually select and copy/paste each row) so that it has empty rows between each data-row, like so:

1
****empty row****
2
****empty row****
3
****empty row****
4

or

1
****2 empty rows****
2
****2 empty rows****
3
****2 empty rows****
4

etc.?

MORE DETAIL IN COMMENTS BELOW IF REQUIRED

(Slightly edited by @Lupp for better readability.)

edit retag flag offensive close merge delete

Comments

What do you want to achieve in the end?

Lupp gravatar imageLupp ( 2021-02-06 00:54:37 +0200 )edit

You could enter =MOD(ROW(),2) in an unused column for double the number of rows as data, Autofilter for 1, paste your data in another column, then unfilter. Replace 2 by 3 for spacing with two empty rows

Rather simpler would be to enter in an unused column say X, make X1 1, X2 leave blank, X3 2 then drag down, and filter as above.

Earnest Al gravatar imageEarnest Al ( 2021-02-06 01:44:20 +0200 )edit

@Lupp I have a list of formulae with cell references that increase in increments of a single row, positioned directly above each other in the next row, e.g:

ROW #. | FORMULA

1) Formula 1 A1

2) Formula 1 A2

3) Formula 1 A3

I want these formulae to be arranged so that there are two empty rows between each, with the cell references still addressing the same cells (without having to lock the row references and copy/paste each manually in the right position, or manually add extra rows one by one, etc.- a "fast" solution), e.g:

1) Formula 1 A1

2)

3)

4) Formula 1 A2

5)

6)

7) Formula 1 A3

etc.

appreciatethehelp gravatar imageappreciatethehelp ( 2021-02-06 05:49:59 +0200 )edit

The information that you have formulas needing to adapt in a way is relevent and was missing in the question.
"...with the cell references still addressing the same cells..." To me it's unclear what "same cells" is meant to mean in the context. After all any solution may need to "move" something. Shall this be interpreted as moving the cells themselves in an "eternal grid" (same cell changes address) or as moving contents (same cell keeps address).
Anyway my question in return was "What do you want to achieve in the end?"
Generally spreadsheets needing to have something or to do something "every other row" or "every fifth row" should be suspected to be designed badly. I'm not so fond of supporting bad design by workarounds.
(Hoping you won't blame me for acting on opinions.)

Lupp gravatar imageLupp ( 2021-02-06 13:14:02 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2021-02-06 14:49:11 +0200

m.a.riosv gravatar image

The copy it's easy. E.g. copy A1:A3 and paste in A4:A100, that's all.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-02-06 00:39:32 +0200

Seen: 30 times

Last updated: Feb 06