1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Long row formula

Discussion in 'Ask an Excel Question' started by sms2luv, Jul 29, 2017.

  1. sms2luv

    sms2luv Member

    Messages:
    270
    Hi all,

    I remove attendance data from my intranet site for the production floor.

    The data has data, Emp I'd and some other info.
    Starting from column A to Y.

    The data is small at start of the month, but increases as the month end.

    So I will count the rows, its sometimes 3000 and at the end of the month it becomes around 13000.

    I have used Vlookup to get name from Emp I'd.
    I have used text formula to get Week day, Week num, Month, Month num, based on date.

    So I have copied the formula from row 2 till row 20000(for safer side in case the data may increase)

    This has increased the size of the entire work book.

    So my question is whether adding too many formula takes more space?

    The raw dump size is just 800kb but after adding formulas the size comes to 3.5 mb in binary format.

    Is there any alternatives that will paste the formulas only till the last used cell.
  2. AliGW

    AliGW Member

    Messages:
    59
    If you convert your data to Excel table format, then the formulae should populate down each time you add a new row. It depends very much on the nature of the formulae, too: volatile functions such as INDIRECT and heavy array formulae, if overused, can slow things down dramatically.
    Marc L likes this.
  3. PaulF

    PaulF Active Member

    Messages:
    203
    If you upload a file I can provide a vba on change solution if you don't want to convert your data to a table.
  4. sms2luv

    sms2luv Member

    Messages:
    270
    Uploading Sample file.
    Usually rows length goes up to 15000.

    Attached Files:

  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,060
    Hi ,

    Can you explain what is the further processing that is done on the data in this workbook ?

    What is the purpose of inserting the formulae in columns T through AF , alongside the data ?

    Narayan
  6. sms2luv

    sms2luv Member

    Messages:
    270
    I create a pivot for this sheet.
    Purpose of inserting formula till AF is that the rows are now limited, but as the data increases the row count will also increase
    To avoid copy pasting formula I am doing this
  7. sms2luv

    sms2luv Member

    Messages:
    270
    Wanted to know, if I use Vlookup with For loop, then I would save on the file size.
    Because I am not writing formula in each and every cell.
    Could you please help.
  8. AVK

    AVK Active Member

    Messages:
    195
    Instead of each row why not using summary for single employee of particular day. Look attach file summary sheet.

    Attached Files:

  9. sms2luv

    sms2luv Member

    Messages:
    270
    Well, i guess this will not do the work of a pivot.
    We need leave count, absent count, Present count
    Then we need shrinkage %(leave+absent)/(leave+present+present)
    Then we need day by day shrinkage
    Manager wise
    Date wise
    Emp wise
    Segment wise
    Highest to Lowest wise
    Pl leave, Ul leaves, SL Leaves
    So there are several other things which i guess only pivot table can do.
    If you will are Present,Leave,Absent,Off.
    It has a long formula, which I guess will increase file size.
    So I think that we can use Macros to reduce size as formula will not be needed.
  10. PaulF

    PaulF Active Member

    Messages:
    203
    Check out attached file and see if this works for you. Any edit on Sheet2 till trigger the VBA to replace the formulas in the rows that have data.

    Attached Files:

  11. sms2luv

    sms2luv Member

    Messages:
    270
    Hi, I didn't understand the concept.
    The formula are still there in the sheet, which will increase the size.
    Please don't mind, can you tell me what changed here.
  12. PaulF

    PaulF Active Member

    Messages:
    203
    Add some new data and or delete the formulas...
  13. sms2luv

    sms2luv Member

    Messages:
    270
    OK, I will try this.
    I had a question in my mind about excel file size.
    For example if I create a new excel file.
    1) I add around 100 words in a cell.
    2) I added some formula which is also of 100 words.
    So will the file size remain the same.
  14. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,890
    Hi, sms2luv!
    I'd bet on Excel working on a different unveiled way. And unknown for me.
    Regards!
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,060
    Hi ,

    Can you explain why you are so critical of the file size ?

    A user is generally concerned about recalculation time ; file size , unless it goes beyond a few MB is not a major concern.

    What is the maximum size of your file , with all the data in it , that is acceptable ? 3.5 MB is not so big ; how much time does it take to open / close ?

    Narayan
    Last edited: Aug 2, 2017
  16. sms2luv

    sms2luv Member

    Messages:
    270
    Size goes to 35 mb in Binary format
  17. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,060
    Hi ,

    Definitely it cannot be 35 MB merely from 15000 rows of data over 30 columns.

    Can you upload at least a scaled down version having 1000 rows of data and formulae ?

    Narayan
  18. sms2luv

    sms2luv Member

    Messages:
    270
    There are 8 sheets, data goes from Column A to BD.
    And till row 48000.
    This usually happened if I have legacy data.
  19. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,060
    Hi ,

    Why are there 8 worksheets ?

    Is the data in any one worksheet related to the data in another worksheet ?

    Narayan
  20. sms2luv

    sms2luv Member

    Messages:
    270
    We have several parameters like.
    Quality
    Customer Satisfaction
    Attendance
    Average Handling Time
    Aux Report.
    Login/Logout
    Calls in & out data
    Service level.

    I checked the file, really make good sense that I don't have to paste the formulas anymore.

    So, once we put the data, it will automatically put the formula in the cells.
    I was thinking rather then putting the formula in cells via VBA, don't you think that it would be better if we directly add it as Value.
    This will infact makes the file run faster.
    In case of using formula, excel will calculate the formula every time.
    Please suggest.
  21. sms2luv

    sms2luv Member

    Messages:
    270
    The file worked fine.
    I added more data and it copied the formula till the last used row.
    I deleted some values from some rows and got run time error, after I added some more data and it was not pasting the formula automatically.
  22. sms2luv

    sms2luv Member

    Messages:
    270
    Instead of using worksheet change event, can we use For loop.
    If yes, please help with the code.

Share This Page