Results 1 to 14 of 14

20150430, 11:19 #1
 Join Date
 Apr 2015
 Posts
 7
 Thanks
 3
 Thanked 0 Times in 0 Posts
Excel 2010  Formula to get result of basic sum and replicate into every 5th column
Hi, new guy here so please be kind.
Using Excel 2010 at work so can't download any addional programmes, I'm looking for a formula for the following problem:
I have basic figures in cells A2,B2,C2,D2 with E2 being blank (and every 5th column afterwards is blank across the spreadsheet).
I want to do a sum for D2 minus C2 with result appearing in E2 which is simple. However I want to repeat this formula to go the full width and if possible full depth of the spreadsheet and populate every blank 5th column so the next sum would be I2 minus H2 appearing in J2 and so on..... The report has more than 7000 columns and 1000 rows so I can't just copy and paste the basic formula into every 5th (blank) column.
Hope this make sense and any help is appreciated.

20150430, 11:43 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
If the cells for the formulas are the only blank cells, select all your data, press f5, then Special..., and select blanks and click OK. Now enter =D2C2 and press Ctrl+Enter.
Regards,
Rory
Microsoft MVP  Excel

20150430, 17:07 #3
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,949
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi udders
Welcome to the Lounge!
Rory's method is fast, effective and efficient.
But if you do have other blank cells within your data range, then we can provide a vba routine that would just copy the formulas to the required columns.
It wouldn't be as quick though. Might take a minute or so.
zeddy

20150501, 03:27 #4
 Join Date
 Apr 2015
 Posts
 7
 Thanks
 3
 Thanked 0 Times in 0 Posts

20150501, 04:02 #5
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,949
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi udders
This code will do it in a JIF
(JIF = column 7000)
Code:Sub copyFormulas() 'for speedup.. Application.ScreenUpdating = False Application.Calculation = xlCalculationManual zLastRow = Cells(Rows.Count, "A").End(xlUp).Row zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column [e2] = "=d2c2" 'put formula into cell [E2] temp = "e2:e" & zLastRow 'formula copyto range; e.g. "e2:e1200" [e2].Copy Range(temp) 'copy formula to range zColsToDo = Int(zLastCol / 5) 'every 5th column counter = 0 'number completed For i = 10 To zLastCol Step 5 'start at column 10;=[J]; [E] already done Range(temp).Copy Cells(2, i) 'copy formula range to column counter = counter + 1 'number completed Application.StatusBar = "Processing " & counter & " of " & zColsToDo Next 'process next column Application.StatusBar = "updating calcs.." Application.Calculation = xlCalculationAutomatic Application.StatusBar = False [a1].Select MsgBox ("DONE!") End Sub
In Excel2007 it does it very very fast (couple of seconds)
On my Excel2010 it is a bit slower, and the progress statusbar stops displaying after ~300 columns done (but it does complete!)
On Excel2013, much slower than Excel2007, but shows the statusbar progress till the end.
If you have a choice, I would do it on a PC with Excel2007.
Make sure you do it with a copy of your file!
zeddy

20150501, 05:15 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
This should be quite a bit faster, especially in the slower versions:
Code:Sub copyFormulas() 'for speedup.. Application.ScreenUpdating = False Application.Calculation = xlCalculationManual zlastrow = Cells(Rows.Count, "A").End(xlUp).Row zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column zColsToDo = Int(zLastCol / 5) 'every 5th column counter = 0 'number completed For i = 5 To zLastCol Step 5 'start at column 10;=[J]; [E] already done With Cells(2, i) .FormulaR1C1 = "=RC[1]RC[2]" .Resize(zlastrow  1).FillDown End With counter = counter + 1 'number completed Application.StatusBar = "Processing " & counter & " of " & zColsToDo Next 'process next column Application.StatusBar = "updating calcs.." Application.Calculation = xlCalculationAutomatic Application.StatusBar = False MsgBox ("DONE!") End Sub
Last edited by rory; 20150501 at 05:22.
Regards,
Rory
Microsoft MVP  Excel

The Following User Says Thank You to rory For This Useful Post:
zeddy (20150501)

20150501, 06:09 #7
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,949
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi Rory
Magic!
Here's the updated file.
I also fixed it to do the very last column [JIF] which was missed.
zeddy

20150506, 09:34 #8
 Join Date
 Apr 2015
 Posts
 7
 Thanks
 3
 Thanked 0 Times in 0 Posts
Hi guys, thanks for the replies. Been on training the last few days and not had a chance to say thanks for your help.
Ok.... the spreadsheet has evolved slighty but wont change again. Columns A  AA have fixed data that I don't want to amend.
The five columns now start from AB2 with the sum slightly changed.
It would now be AF2=AD2AE2 then AK2=AI2AJ2 and so on as per first post.
I've attached an example to try and make it a bit clearer.

20150506, 10:13 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
You should just have to change this:
Code:For i = 5 To zLastCol Step 5
Code:For i = 32 To zLastCol Step 5
Regards,
Rory
Microsoft MVP  Excel

20150506, 12:21 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,949
 Thanks
 140
 Thanked 519 Times in 495 Posts
..and, as you have changed the subtraction order:
Code:.FormulaR1C1 = "=RC[2]RC[1]" 'e.g. AF=ADAE
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
udders (20150507)

20150507, 04:17 #11
 Join Date
 Apr 2015
 Posts
 7
 Thanks
 3
 Thanked 0 Times in 0 Posts

20150507, 04:19 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,286
 Thanks
 3
 Thanked 194 Times in 180 Posts
Change this line:
Code:zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Code:zLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Regards,
Rory
Microsoft MVP  Excel

The Following User Says Thank You to rory For This Useful Post:
udders (20150507)

20150507, 04:37 #13
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,949
 Thanks
 140
 Thanked 519 Times in 495 Posts
Hi udders
If you prefer to specify your start and end columns, use this code:
Code:Sub copyFormulas() 'for speedup.. Application.ScreenUpdating = False Application.Calculation = xlCalculationManual zlastrow = Cells(Rows.Count, "A").End(xlUp).Row zColsToDo = Int(zLastCol / 5) 'every 5th column counter = 0 'number completed For i = [AF1].Column To [RGL1].Column Step 5 '<< start and end columns With Cells(2, i) .FormulaR1C1 = "=RC[2]RC[1]" 'e.g. AF=ADAE .Resize(zlastrow  1).FillDown End With counter = counter + 1 'number completed Application.StatusBar = "Processing " & counter & " of " & zColsToDo Next 'process next column Application.StatusBar = "updating calcs.." Application.Calculation = xlCalculationAutomatic Application.StatusBar = False MsgBox ("DONE!") End Sub
zeddy

20150507, 04:37 #14
 Join Date
 Apr 2015
 Posts
 7
 Thanks
 3
 Thanked 0 Times in 0 Posts