Forum Discussion
Spreadsheet limits
One of Fredrikstad has space at the end (#591, "Fredrikstad "). I added TRIM to the formula
=IFERROR(INDEX(By_Range,MATCH(0,COUNTIF($D$3:D3,TRIM(By_Range)),0)),"")
and use SUMPRODUCT to count
=SUMPRODUCT((TRIM(Kupong_Reg!$C$2:$C$986)=D4)*1)
That's in columns D, E in the attached file
Thank you for all your help Sergei. But I'm right back to where I started. It doesn't work on my large file. And large must be very relative here. I have been looking at Excel limits, this is far from hitting any of them. I'm running 64-bit WIN7 on a Intel i7 2,5GHz with 8GB of RAM. What could be the problem? Is it a bug clean and simple?
Can I send you the complete file? I'll just fill in some bogus data.
It depends on which version of Office you are. Microsoft mainly updates and give information about Office365, all other versions are far behind.
What's new is usually published on this site (Excel blogs), in particular https://techcommunity.microsoft.com/t5/Excel-Blog/Excel-performance-improvements-now-take-seconds-running-Lookup/ba-p/254199.
Updates for Office365 are here and https://techcommunity.microsoft.com/t5/Excel-Blog/Excel-performance-improvements-now-take-seconds-running-Lookup/ba-p/254199 and around.
You may compare with similar information for Office 2016 https://docs.microsoft.com/en-gb/officeupdates/office-updates-msi
Ok... Understand. Array functions are slow. If MS is working on it, and we can expect Excel product increments coming soon. I'll just wait.
As you saw, there aren't many cities added at this time. And I don't expect many new ones added before the end of 2019. I'll add them manually for now. And just keep waiting for new versions of Excel coming out.
Power Queries does not justify the effort right now. This function is "nice to have" at the moment, and everything else works.
Again, thank you so much for spending time on this.
Is there a link to where MS publishes new product increments and what has improved (version history)?
Regards
MortenMorten, that's formulas with arrays are quite slow. Microsoft recently dramatically improved performance, but they only started with deployment. Plus your configuration is not advances, however I don't think you could significantly improve the performance only by changing it.
Alternative could be Power Query, that's a quite simple query to do the task (in attached). Plus better if you transform your source data into table (Ctrl+T).
Without testing it's hard to say what will be actual performance, but it shall be faster.