is there a quick way to put spaces in data?

[jvoortman]

is there a quick way to put spaces in data? I have a Excel Spreadsheet
that has data in it which was inported there. There is info in columns
a b c d and in rows 1 through to the end (thousands). I need to get a
space inserted after every 3 lines of data. So it would be A,1-2-3-4
then B, 1-2-3-4 then c, 1-2-3-4 then blank 1,2,3,4, then 3 rows of info
followed by a blank, and so on. I posted a sample at the address below:

http://lmr7.homestead.com/index.html

thanks in advance for any help offered!!!!!!!!

 

[Bryan Hessey]

Select a blank column (I use column E)

in E1 put 2
in E2 put =IF(MOD(E1,4)=0,E1+2,E1+1)
select E2 and formula copy this to the end of your data
(ie, drag the small block + in the bottom right corner of the selected
E2 downwards)

After the end of your data, find a blank row
in column E of that row put 5
in column E of the next row put =E9999+4 where 9999 is the row you put
the 5
Formula-drag this down far enough so that the number is higher than the
number generated in part 1

Select column E and Copy
Select column E and Paste Special - Values (back over itsself)

select all data (click the block above the 1 in row 1, and left of A in
the column heading)

Sort over column E

Delete column E

Hope this helps

 

[jvoortman]

I posted the results at the web page, must've done something wrong,
please help again

 

[B. R.Ramachandran]

Hi,

Let's suppose that your data are in A2:A1000 in Sheet 1. Enter the
following formula in A2 of Sheet 2.

=IF(MOD(ROW($A2)-1,4)=0,"",OFFSET(Sheet1!A$2,QUOTIENT(ROW($A2)-1,4)*3+MOD(ROW($A2)-1,4)-1,0))

Drag the formula across as many columns as you have in Sheet 1 (e.g., A2
....D2)
Then select A22 and drag the formula down the rows (as many as you want).
Once the original data are exhausted, the formula would put 0's at the bottom
rows.
Once all the data from Sheet 1 have been transferred to Sheet 2, select the
entire data are, "Edit" --> "Paste Special" --> "Values", to make the data
independent of Sheet 1.

If your first row is not in Row 2 but Row x (e.g., Row 5), modify the
"Sheet1!$A2" in the OFFSET part of the formula accordingly (i.e.,
Sheet1!$A5). If the first row where you start your your formatted data (in
Sheet 2) is not Row 2 but Row y (e.g., Row 6), change the "ROW($A2)-1" parts
of the formula (there are three of them) to "ROW($A6)-5".

Regards,
B. R. Ramachandran

 

[Bryan Hessey]

B. R.Ramachandran set a different method, but if you had trouble with m
version you would perhaps struggle with that also.

------------------------------------------------------

Looking at you results, and attempting to guess what you did wrong .
you didn't follow the formula.

When it is requested that you 'drag a formula' it means that you clic
the small square in the bottom right corner and drag the formula dow
the column. The result of the formula will then reflect in the cell
that you dragged the formula over.

That you have so few entries in column E indicates that you did no
follow the instructions, so here again :::

in E1 put 2
means click on Cell E1 and type 2 and press Enter

in E2 put =IF(MOD(E1,4)=0,E1+2,E1+1)
means click on cell E2 and enter the formula shown
=IF(MOD(E1,4)=0,E1+2,E1+1)
a number 3 should appear in the cell

select E2 and formula copy this to the end of your data
(ie, drag the small block + in the bottom right corner of the selecte
E2 downwards)
drag the formula to the end of your data, the cells in the E colum
from E1 to the end of your data should be a count from 2 to as many a
you need but missing every 4th number, thus the cells should show 2, 3
4, 6, 7, 8, 10,11, 12, 14,15, 16, 18, 19, 20, 21 etc etc down to the en
of your data.

Make a note of the number shown in column E of the last row of you
data.



After the end of your data, find a blank row
in column E of that row put 5
means find the first blank rown after your data, and in the E cell o
that row type a 5 and press Enter.
5 should reflect in the cell

in column E of the next row put =E9999+4 where 9999 is the row you pu
the 5
Formula-drag this down far enough so that the number is higher than th
number generated in part 1

I thought this was easy, ie, put a 5 in E of the first blank, and th
formula in the next row, referencing the row where you put the 5
This next row should then show a 9

Each row afterwards the number should increase by 4, showing
5, 9, 13, 17, 21, 25, 29 etc etc
and should have enough numbers to be 1 more than the number you made
note of before.

You should now have a column E that has numbers 2 to 'whatever' wher
every 4th number is a blank line which is after your data.

Just 'fix' those numbers for sorting by:

Select column E and Copy
Select column E and Paste Special - Values (back over column E)


and now you can sort all data over column E - viz:


select all data (click the block above the 1 in row 1, and left of A i
the column heading)

Sort over column E

Delete column E


You should be able to do this.

 

[jvoortman]

oh so close, please be patient with... I posted "sample 4" at web page
and as you will see it started to accomplish what I wanted but for some
reason it didn't do it to all the data.

 

[jvoortman]

Just tried it again, and it worked great. Thanks for the help. I don't
know where I went wrong eralier, but all I care about is that it works
now. THANKS THANKS etc

 

[jvoortman]

I posted it as sample 5 on web page just in cases someone was following
along and wanted to see the end result.

 

[Bryan Hessey]

Spot on, and good to see that you got it working!

I posted it as sample 5 on web page just in cases someone was following
along and wanted to see the end result.