Forum Discussion

MortenHa_69's avatar
MortenHa_69
Copper Contributor
Nov 26, 2018

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond 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_69's avatar
      MortenHa_69
      Copper Contributor
      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.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

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

        Will play with it if another formula works better.

Resources