SOLVED

# Excel file size is OUT OF CONTROL

Copper Contributor

# Excel file size is OUT OF CONTROL

I'm creating a workbook to record Rebar lengths and quantities.

All lengths are recorded in decimal feet (ie 1'-1" = 1.083)
The final result needs to be read as FT-IN (ie 5-7 [no fractions or decimal or special characters]) and formatted into a table that conforms to a certain standard.
What I have works-- but the file is absolutely massive and SLOW. I don't know how to more efficiently format it.
I don't see away to attach my file... hopefully I can do so after I've posted.
The current structure goes like this.
Input Page

Green headers are manual entry orange headers handle conversions and basic math.
TOT_LNGTH column is simply =C2*D2
BAR-LENGTH_OUTPUT is =(ROUNDDOWN(D2,0)&"-"&ROUND(((D2-ROUNDDOWN(D2,0))*12),0))
B lngth and c lngth are similar

the final column looks like this  =IF(ROUND(((H2-ROUNDDOWN(H2,0))*12),0)>11,(ROUNDDOWN(H2,0)+1&"-"&0),(ROUNDDOWN(H2,0)&"-"&ROUND(((H2-ROUNDDOWN(H2,0))*12),0)))

That data is then pulled into the following worksheet with the proper format/structure necessary for the excel doc to referenced by AutoCAD

basically all cells (except the marks column) are filled with a formula similar to B4 which is =FILTER(BAR,A4=MARK,"")

BAR and MARK are both defined names from columns on that first input sheet.

Before data entry this file is 5.81MB and basically only works with Auto-Calculation turned off and I have close to 300 different bars to record...
What's the best way to optimize this mess?

Any help is appreciated. Thanks!

5 Replies

# Re: Excel file size is OUT OF CONTROL

I am able to upload my file if anyone can inform me how

# Re: Excel file size is OUT OF CONTROL

@CDusty Save the file in OneDrive, Dropbox or something similar and post the link that gives access to it here. And don't forget to mention which Excel version you use.

# Re: Excel file size is OUT OF CONTROL

best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Excel file size is OUT OF CONTROL

@CDusty I reduced the file size from 6MB to 47KB by removing 'abusive' use of the FILTER function in the output sheet. That allowed me to get rid of all the named ranges spanning entire columns in the input sheet. By the way, I also made a small adjustment to the input sheet so that FILTER can spill across multiple cells into the output sheet, rather than cell by cell. Auto calculation is on and it calculates instantly.

Very likely that you can optimize even more, but I didn't go that far as the problem seems to be resolved by these minor adjustments.

# Re: Excel file size is OUT OF CONTROL

And I learned something new 😄

1 best response

Accepted Solutions
best response confirmed by mathetes (Silver Contributor)
Solution

# Re: Excel file size is OUT OF CONTROL

@CDusty I reduced the file size from 6MB to 47KB by removing 'abusive' use of the FILTER function in the output sheet. That allowed me to get rid of all the named ranges spanning entire columns in the input sheet. By the way, I also made a small adjustment to the input sheet so that FILTER can spill across multiple cells into the output sheet, rather than cell by cell. Auto calculation is on and it calculates instantly.

Very likely that you can optimize even more, but I didn't go that far as the problem seems to be resolved by these minor adjustments.