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!!!!!!!!

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 Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=476385

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

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 A2:D2 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

"jvoortman" wrote:

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!!!!!!!!

B. R.Ramachandran set a different method, but if you had trouble with my
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 click
the small square in the bottom right corner and drag the formula down
the column. The result of the formula will then reflect in the cells
that you dragged the formula over.

That you have so few entries in column E indicates that you did not
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 selected
E2 downwards)
drag the formula to the end of your data, the cells in the E column
from E1 to the end of your data should be a count from 2 to as many as
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 end
of your data.

Make a note of the number shown in column E of the last row of your
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 of
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 put
the 5
Formula-drag this down far enough so that the number is higher than the
number generated in part 1

I thought this was easy, ie, put a 5 in E of the first blank, and the
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 a
note of before.

You should now have a column E that has numbers 2 to 'whatever' where
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 in
the column heading)

Sort over column E

Delete column E


You should be able to do this.


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


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=476385

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.

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

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

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

jvoortman Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=476385