I am inserting this formula in my excel sheet.
There exists however more than 6000 rows and it takes therefore an awful lot of time to process.
Please Login or Register to view this content.
Is there a way to get the result rather than the formula itself in the cell?
Last edited by alansidman; 03-12-2021 at 10:37 AM.
Untested as no sample file...
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
I would suggest COUNTIF would be more efficient unless you have a specific need to avoid it. If you do, then you should try and limit the number of rows used in your range as that is what slows the formula down.
Rory
Hello Sintek,
Thank you for trying.
it did not work however.
It returned #VALUE!
I enclosed a sample file.
Last edited by Jonathan78; 03-13-2021 at 02:35 AM.
Hello Rorya,
Most values exceed the 255 character limit.
SUMPRODUCT seems to be the only one being able to handle this
In that case you should try to limit the rows involved in the formula. Your current version has to check every row on the sheet.
Apologies...i missed that you were trying to input into range and not individual cell...What are you actually wanting to achieve...The sample file and formula provided has no connection?
The formula in the sample file is a little bit different than the previous provided formula because I had to simplify things.
The idea is the same:
In sheet "NEW" the result of a formula needs to be inserted in column E.
The formula checks if the value on sheet "NEW" in column D als exists somewhere in column D on sheet "OLD"
If it does exists the outcome will be true else it will be false
Because many cells exceed 255 characters, many formulas will not work like:
=COUNTIF(range,value)>0.
=IF(COUNTIF(range,value),"Yes","No")
=COUNTIF(A1:A100,"*"&C1&"*")>0.
=ISNUMBER(MATCH(value,range,0))
etc.
One that does still work eventhough it exists the 255 character limit is SUMPRODUCT.
Therefore I would like to use that one.
Another thing to take into consideration is that my actual file contains thousands of rows.
So when I insert the formula, rather than the result of the formula, It will take very long to run the macro.
Therefore I'd like to insert the result value of the SUMPRODUCT formula by means of VBA
So...Upload a sample file showing the expected result...manually and I'm sure a solution can be provided...
Perhaps match formula
Edit ... my bad only now noticed exceeds 255 characters...match no work
Last edited by sintek; 03-13-2021 at 02:57 AM.
I enclosed a new file with the desired outcome:
So the fllowing gives me the desired outcome, but is inserted as a formula, rather than the outcome of a formula and therefore having a long runtime
This is what I have tried so far, but is returning #VALUE!, so I must be doing something wrong herePlease Login or Register to view this content.
Please Login or Register to view this content.
Please Login or Register to view this content.
Yep...Dictionary to the rescue...
Hello Jindon,
Thank you for trying, but looping is not an option.
My workbook contains thousands of rows.
I already tried looping but takes ages to run.
Did you actually teste the code?
Calculating such bulk data with CountIf/Sumproduct takes ages, but the array and the dictionary is the best.
And you should forget the myth about the LOOP.
Loop is very fast. It depends on how you use it.
Last edited by jindon; 03-13-2021 at 03:36 AM.
Thanks again Jindon!
I turned out to be must faster than expected.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks