Results 1 to 14 of 14
  1. #1
    New Lounger
    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.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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 =D2-C2 and press Ctrl+Enter.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 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

  4. #4
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    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
    Hi zeddy,

    Unfortunately there are other blank cells within the spreadsheet so the fix Rory kindly provided will not work.

    Thanks for your replies.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 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] = "=d2-c2"             'put formula into cell [E2]
    
    temp = "e2:e" & zLastRow    'formula copy-to 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
    I have attached a demo file with 7000 columns of data to test on.
    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
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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
    It will be faster still if you skip the statusbar code.
    Last edited by rory; 2015-05-01 at 05:22.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following User Says Thank You to rory For This Useful Post:

    zeddy (2015-05-01)

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Rory

    Magic!
    Here's the updated file.
    I also fixed it to do the very last column [JIF] which was missed.

    zeddy
    Attached Files Attached Files

  9. #8
    New Lounger
    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=AD2-AE2 then AK2=AI2-AJ2 and so on as per first post.

    I've attached an example to try and make it a bit clearer.
    Attached Files Attached Files

  10. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You should just have to change this:
    Code:
    For i = 5 To zLastCol Step 5
    to this:
    Code:
    For i = 32 To zLastCol Step 5
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    ..and, as you have changed the subtraction order:
    Code:
    .FormulaR1C1 = "=RC[-2]-RC[-1]"         'e.g. AF=AD-AE
    ..see updated file

    zeddy
    Attached Files Attached Files

  12. The Following User Says Thank You to zeddy For This Useful Post:

    udders (2015-05-07)

  13. #11
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    You should just have to change this:
    Code:
    For i = 5 To zLastCol Step 5
    to this:
    Code:
    For i = 32 To zLastCol Step 5
    Thanks so much for your help so far.... This works but stops populating at column IH. Can you work your magic again and see if we can get it to populate the rest of the spreadsheet?

    If it helps, the last column is as far as RGL and last row at the moment is 1091.

  14. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Change this line:
    Code:
        zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    to this:
    Code:
        zLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Regards,
    Rory

    Microsoft MVP - Excel

  15. The Following User Says Thank You to rory For This Useful Post:

    udders (2015-05-07)

  16. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 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=AD-AE
        .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
    ..but you would have to remember to manually update the code if you added more than your current 12,362 columns up to [RGL]

    zeddy

  17. #14
    New Lounger
    Join Date
    Apr 2015
    Posts
    7
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Change this line:
    Code:
        zLastCol = Cells(2, Columns.Count).End(xlToLeft).Column
    to this:
    Code:
        zLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Cheers..... that's worked a treat.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •