Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-05-2022, 04:25 AM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
A 20k lines Sheet calculation takes 1,23 hours!!
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default A 20k lines Sheet calculation takes 1,23 hours!!

The real sheet of the type shown in SAMPLE (attached) has 20k lines and its 100% calculation takes 1 hour and 23 minutes !!!
What is wrong with its formulas? Are there better formulas to achieve the same results
(= show values which exceed the given limit, in this case 3 occurrencies)?


Thanks to anyone who can help!
Attached Files
File Type: xlsx SAMPLE.XLSX (11.6 KB, 13 views)
Reply With Quote
  #2  
Old 11-05-2022, 07:30 AM
fjns fjns is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
 
Join Date: Sep 2022
Location: Hungary, Szeged
Posts: 16
fjns is on a distinguished road
Default

Hi, see in attached file a possible solution...
Attached Files
File Type: xlsx SAMPLE2.xlsx (15.9 KB, 7 views)
Reply With Quote
  #3  
Old 11-05-2022, 07:45 AM
p45cal's Avatar
p45cal p45cal is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Do you need to see the intermediate results in column K:Q?
Why are you using CELL("Contents"… ?
Won't referring to the cell directly do?
=IF(K1<>"-",A1," -")
and if you need to see a leading zero, custom format those cells with 00.
Direct result without intermediate:
=IF(COUNTIFS($A$1:$G$9,A1)>=$I$1,A1, " -")
Reply With Quote
  #4  
Old 11-05-2022, 02:02 PM
fjns fjns is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
 
Join Date: Sep 2022
Location: Hungary, Szeged
Posts: 16
fjns is on a distinguished road
Default

Hi, in the linked file are two tables with 21000 rows.
SAMPLEorig21000rows.xlsx (with your formulas)
SAMPLEnew21000rows.xlsx (with my formulas)
The product of my test (100% calculation):
SAMPLEorig21000rows.xlsx: 3 minutes
SAMPLEnew21000rows.xlsx: 2 seconds

SAMPLEs21000rows.zip - Google Drive

Last edited by fjns; 11-06-2022 at 02:52 AM.
Reply With Quote
  #5  
Old 11-06-2022, 03:13 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
A 20k lines Sheet calculation takes 1,23 hours!!
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Dear P45cal your direct result formula does not decrease the duration of the calculation.
Dear Fjns, I'm VERY SORRY, … to make the sample for you as simple as possible I set up a "2-digit-sized-values array" …
hoping to get a solution for "any digit sized values array" …
As a matter of fact my application happens to deal with a 4-digit-values array as well as a 6-digit-values arrays …
so in this case your solution, limited to a 99 lines help-column, does not seem to work for the 4 and 6 digit values … attached the real type array. AP
Attached Files
File Type: xlsx SAMPLE2.xlsx (17.7 KB, 6 views)
Reply With Quote
  #6  
Old 11-06-2022, 03:45 PM
p45cal's Avatar
p45cal p45cal is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

A formula:
=IF(COUNTIF($A$1:$G$21000,A1)>=$I$1,A1,"-")
in cells K1:Q21000, working on random 4 digit numbers in A1:G21000 (not random generated at each calculation, just plain, static values) takes about a minute and a half here.


Do you have an old machine?
What are you aiming to do (in words)?
Reply With Quote
  #7  
Old 11-06-2022, 05:04 PM
fjns fjns is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
 
Join Date: Sep 2022
Location: Hungary, Szeged
Posts: 16
fjns is on a distinguished road
Default

Hi, in the linked file is the solution for 4 digits.
Each calculation takes 5 seconds.

Loading Google Sheets
Reply With Quote
  #8  
Old 11-07-2022, 04:03 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
A 20k lines Sheet calculation takes 1,23 hours!!
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

hi, P45cal, I find it difficult to understand what happens on my PC, an Acer Aspire with an I7-5500u and 8GB ram ... your solution depending on type of changes sometimes works sometimes not ... e.g. when I try to modify the filter limit o to free the first line to insert a description. Due to limits for uploadable files I re-attach only a small part of the real file (6,5 GB) with your solution ... , can you please verify yourself? thank you.
Dear Fjns, the same happens with your solution with the full 20k lines file ... also for you re-attached part of related file (8GB). You not being able to see the real file will probably make useless any effort to solve the problem ... try to increase yourself the file up to 20k lines ... and hope to identify the problem and solve it ... In any case, Thank you both very much for your help. AP
Attached Files
File Type: xlsx P45cal-SAMPLE-last-.xlsx (463.7 KB, 6 views)
File Type: xlsx Fjns-new21000rows4digits.xlsx (450.5 KB, 5 views)
Reply With Quote
  #9  
Old 11-08-2022, 03:24 AM
fjns fjns is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
 
Join Date: Sep 2022
Location: Hungary, Szeged
Posts: 16
fjns is on a distinguished road
Default

Hi, 10000 cells are required in columns J-K regardless of the other columns.
Attached Files
File Type: zip Fjns-new21000rows4digitsRepair.zip (452.9 KB, 7 views)
File Type: xlsx Fjns-new21000rows4digitsRepair2.xlsx (242.9 KB, 4 views)
Reply With Quote
  #10  
Old 11-08-2022, 10:59 AM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
A 20k lines Sheet calculation takes 1,23 hours!!
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Dear Fjns, I've noted that apparently you gave me the same function as suggested previously, but this time the 20k lines calculation went surprisingly and satisfactorily fast:
2 seconds by changing the minimum selection limit value, 4 minutes to eliminate or insert a line, also 4 minutes to change a formula in a cell! Which factor has triggered such a beautiful result will remain for me a distressing mistery! Yet the solution of this very penalizing problem of mine make me greatly happy. ... also the solution suggested by P45cal worked equally fast ... therefore a very very very big thank to both. Your grateful AP
Reply With Quote
  #11  
Old 11-13-2022, 07:20 AM
p45cal's Avatar
p45cal p45cal is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by AP41-at-OfficeFORUM View Post
sometimes works sometimes not ... e.g. when I try to modify the filter limit o to free the first line to insert a description.
I'm not sure what you mean by this statement?
Reply With Quote
  #12  
Old 11-13-2022, 02:04 PM
AP41-at-OfficeFORUM AP41-at-OfficeFORUM is offline A 20k lines Sheet calculation takes 1,23 hours!! Windows 10 A 20k lines Sheet calculation takes 1,23 hours!! Office 2019
Novice
A 20k lines Sheet calculation takes 1,23 hours!!
 
Join Date: May 2021
Location: Italy
Posts: 19
AP41-at-OfficeFORUM is on a distinguished road
Default

Dear 45Pcal, wanting to select only values which were >= of a given selection filter, by changing this filter the solution run ok ... by inserting a new line above the first existing line to input some descriptions the sheet had to be stopped because for some unknown reasons it was taking a lot of time. But do not worry ... now everything runs ok. Thank you! AP
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to adjust Project Duration for Summer and Winter Hours - based on number of hours per day Lou C Project 2 10-03-2021 09:35 AM
Complicated calculation of hours worked APreston Excel 1 05-09-2016 04:42 AM
A 20k lines Sheet calculation takes 1,23 hours!! Very complicated calculation of total hours worked (PLEASE HELP) APreston Excel 1 05-08-2016 02:58 AM
Complex calculation from multiple sheets to master sheet inreality01 Excel 49 01-06-2016 03:09 PM
A 20k lines Sheet calculation takes 1,23 hours!! Help with 'hours worked' calculation... Snvlsfoal Excel 1 08-11-2011 05:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft