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. ...
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.
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.
Yes, I tried to reproduce on a model - it stays forever on calculations.
Will play with it if another formula works better.
- 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?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
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 ?