Spreadsheet limits

[MortenHa_69]

Hi people

I have trouble getting a complex formula to work properly. I'm trying to extract unique values from a large list of city names. The list is R986:C1 long and has empty cells in between. I found this array function on the web:

{=IFERROR(INDEX(Kupong_Reg!$D$2:$D$986; MATCH(0; FREQUENCY(IF(EXACT(Kupong_Reg!$D$2:$D$986; TRANSPOSE($C$1:C1)); MATCH(ROW(Kupong_Reg!$D$2:$D$986); ROW(Kupong_Reg!$D$2:$D$986)); ""); MATCH(ROW(Kupong_Reg!$D$2:$D$986); ROW(Kupong_Reg!$D$2:$D$986))); 0)); "")}

And it worked perfectly when I tested it on a smaller range. R10:C1. 

I'm wondering if the Office 365 Excel app is choking on this one, I have put this function in 100 rows. 

The spreadsheet has additional functions. I believe there are 6000 cells with functions in them. Divided into 4 sheets.

Am I hitting any limits?

Regards

Morten

[#2]

Hi Morten,

Formula looks correct, it extracts unique names from column D of Kupong_Reg. Another story most probably it takes minutes to recalculate it. What exactly doesn't work in your case?

[#3]

Hi Sergei!
Thanks for replying :)

Well, the spreadsheet simply doesn't do any calculations on this formula. Adding another unique city in Column D, does not end up in the list in the C column.

[#4]

Yes, I tried to reproduce on a model - it stays forever on calculations.

Will play with it if another formula works better.

[#5]

Tried this one

{=IFERROR(INDEX(Kupong_Reg!$D$2:$D$986,MATCH(0,COUNTIF($C$1:C1,Kupong_Reg!$D$2:$D$986),0));"")}

It actually worked. except for one entry showing "0". Why do you think that is? Is there a value in the Kupong_Reg!$D$2:$D$986 range with some other formatting?

[#6]

To exclude blank cells

=IFERROR(INDEX(Kupong_Reg!$D$2:$D$986,MATCH(0,COUNTIF($C$1:C1,Kupong_Reg!$D$2:$D$986)+ISBLANK(Kupong_Reg!$D$2:$D$986),0)),"")

but that's also quite slow

[#7]

This error must be a result of some "residual" data behind the scenes in the Kupong_Reg!$D$2:$D$986 range. Been playing around with this spreadsheet for some time now.

Is there a way to "clean up" all the cells in this range? Tried formatting the range to "Standard", but it didn't work. Does Excel provide any other way clean up cells to its "virgin" state ?

[#8]

Do you mean Fredrikstad which appears twice? If so that could be extra space or some invisible character if you copy/paste your list from Web or other source. Could you share this part of your file to check?

[#9]

Thanks for all your help Sergei :)

Here's cut down version of the spreadsheet, all sensitive info removed.

[#10]

This spreadsheet was made using a Norwegian version. I hope the functions come up in English on your computer.

[#11]

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

[#12]

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.

[#13]

Morten, 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.

[#14]

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
Morten

[#15]

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-ru....

Updates for Office365 are here and https://techcommunity.microsoft.com/t5/Excel-Blog/Excel-performance-improvements-now-take-seconds-ru... and around.

You may compare with similar information for Office 2016 https://docs.microsoft.com/en-gb/officeupdates/office-updates-msi