Forum Discussion
Spreadsheet limits
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
14 Replies
- SergeiBaklanDiamond Contributor
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?
- MortenHa_69Copper ContributorHi 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.- SergeiBaklanDiamond Contributor
Yes, I tried to reproduce on a model - it stays forever on calculations.
Will play with it if another formula works better.