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

SAMPLE.XLSX (11.6 KB, 15 views)

Hi, see in attached file a possible solution...

Attached Files

SAMPLE2.xlsx (15.9 KB, 9 views)

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, " -")

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

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

SAMPLE2.xlsx (17.7 KB, 8 views)

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)?

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

Loading Google Sheets

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

	P45cal-SAMPLE-last-.xlsx (463.7 KB, 8 views)
	Fjns-new21000rows4digits.xlsx (450.5 KB, 7 views)

Hi, 10000 cells are required in columns J-K regardless of the other columns.

Attached Files

	Fjns-new21000rows4digitsRepair.zip (452.9 KB, 9 views)
	Fjns-new21000rows4digitsRepair2.xlsx (242.9 KB, 6 views)

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

I'm not sure what you mean by this statement?

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