UNIQUE, FILTER function no more available

%3CLINGO-SUB%20id%3D%22lingo-sub-1188245%22%20slang%3D%22en-US%22%3EUNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188245%22%20slang%3D%22en-US%22%3E%3CP%3ESince%20today%20mid%20afternoon%20(21%2F02%2F2020)%20%2C%20all%20the%20new%20dynamic%20array%20function%20such%20as%20UNIQUE%20and%20FILTER%20suddenly%20desappear%20from%20my%20desktop%20Excel%20(still%20available%20for%20my%20Excel%20On%20Line).%20I%20am%20a%20Office%20365%20user%20and%20I%20didn't%20see%20an%20update%20coming.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20idea%20why%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1188245%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188261%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565470%22%20target%3D%22_blank%22%3E%40Fabien_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20channel%20changes%2C%20especially%20if%20you%20apply%20it%20by%20registry%20key.%20What%20do%20you%20see%20here%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20515px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172632i888079A1AC7A570D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188290%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188290%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188296%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bare%20those%20function%20only%20for%20Office%20Insider%20or%20are%20they%20now%20deployed%20for%20every%20Office%20365%20users%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188299%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188299%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565470%22%20target%3D%22_blank%22%3E%40Fabien_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20no%20access%20to%20monthly%20channel%20(if%20I%20translated%20correctly)%20right%20now%2C%20will%20check%20tomorrow.%20Functions%20shall%20be%20available%20for%20this%20channel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20any%20case%2C%20it's%20better%20to%20send%20a%20frown%20from%20File-%26gt%3BFeedback.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188307%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you.%20You've%20translated%20right%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20make%20me%20think%20about%20a%20language%20problem%20so%20I%20switch%20my%20Excel%20to%20English%20and%20they%20are%20available%20in%20English%20!!%20I%20switched%20back%20to%20french%20and%20they%20are%20there%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWeird%20problem%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20anyways%20!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188339%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565470%22%20target%3D%22_blank%22%3E%40Fabien_M%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20exact%20same%20thing%20happened%20to%20me.%26nbsp%3B%20This%20afternoon%2C%202%2F21%2F2020%2C%20I%20was%20working%20on%20my%20spreadsheet%20using%20the%20new%20dynamic%20arrays.%26nbsp%3B%20I%20closed%20the%20files%20to%20take%20a%20short%20break.%26nbsp%3B%20When%20I%20reopened%20the%20files%2C%20the%20dynamic%20arrays%20were%20gone.%26nbsp%3B%20Formulas%20such%20as%20%3DUNIQUE(%26nbsp%3B%20were%20replaced%20with%20%3D_xlfn.UNIQUE(.%26nbsp%3B%20It%20is%20like%20my%20version%20of%20Excel%20365%20was%20taken%20back%20in%20time%20to%20a%20period%20before%20the%20functions%20were%20available.%26nbsp%3B%20I%20have%20version%202001%20(build%2012430.20288).%26nbsp%3B%20I%20noticed%20on%20the%20Office%20365%20website%20it%20mentions%20the%20latest%20version%20is%202001%20(build%2012430.20184)%20and%20was%20released%20on%202%2F19%2F2020.%26nbsp%3B%20I%20did%20not%20notice%20an%20update.%26nbsp%3B%20I%20reinstalled%20Office%20365%20but%20no%20change.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1188340%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1188340%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565515%22%20target%3D%22_blank%22%3E%40steve37777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFixed%20the%20problem.%26nbsp%3B%20I%20saw%20an%20earlier%20post%20about%20changing%20languages.%26nbsp%3B%20I%20went%20to%20Options%20-%26gt%3B%20Language%20and%20saw%20that%20it%20was%20set%20to%20%22Match%20Microsoft%20Windows%22%20and%20I%20changed%20it%20to%20%22English.%22%26nbsp%3B%20And%20the%20new%20functions%20are%20back.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375303%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375303%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F565515%22%20target%3D%22_blank%22%3E%40steve37777%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eone%20year%20later%20(now%2021st%20May%202021)%20and%20I%20suddenly%20have%20the%20same%20problem.%20%26nbsp%3BI%20have%20been%20using%20%22unique%22%20and%20%22filter%22%20in%20formulas%20for%20quite%20sometime%20and%20this%20morning%2C%20I%20open%20one%20workbook%20and%20I%20get%20the%20same%20message%20as%20you%20%22(_xlfn.UNIQUE(_xlfn._xlws.FILTER(%22...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20making%20every%20change%20in%20the%20book%20to%20the%20language%20settings%20(I'm%20on%20a%20Mac%20working%20in%20France%20but%20with%20UK%20English%20as%20my%20setting)%20and%20nothing%20works%20now.%20%26nbsp%3BI%20did%20not%20change%20anything%20on%20the%20settings%20since%20stopping%20work%20last%20night%20and%20rebooting%20this%20morning.%20%26nbsp%3BI%20was%20on%20the%20Excel%20Beta%20Channel%20but%20have%20gone%20back%20down%20to%20the%20%22current%22%20channel.%20%26nbsp%3BAll%20to%20no%20avail.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20have%20any%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376153%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059894%22%20target%3D%22_blank%22%3E%40RvL_richy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20ideas.%20If%20you%20change%20no%20one%20setting%2C%20reboot%20and%20channel%20was%20changed%20-%20looks%20very%20strange.%20Why%20don't%20you%20to%20shift%20on%20previous%20channel%20back%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376259%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%2C%20FILTER%20function%20no%20more%20available%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376259%22%20slang%3D%22en-US%22%3EHey%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20not%20sure%20to%20fully%20understand%20what%20you%20have%20written.%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20write%3A%20%22No%20ideas.%20If%20you%20change%20no%20one%20setting%2C%20reboot%20and%20channel%20was%20changed%20-%20looks%20very%20strange.%22%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20I%20interpret%20that%20you%20too%20have%20no%20idea%20how%20this%20could%20happen%3F%20You've%20understood%20what%20I've%20said%20and%20it%20all%20sounds%20very%20strange%20to%20you%3F%20You've%20noted%20that%20I%20did%20not%20change%20anything%20but%20after%20rebooting%2C%20Excel%20stopped%20working%20as%20before%3F%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20I%20also%20understand%20that%20you%20propose%20I%20%22change%20channels%22%20-%20that%20I%20go%20back%20to%20the%20%22standard%22%20channel%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I've%20understood%20what%20you%20were%20asking%20%2F%20proposing%2C%20yes%20you%20are%20right.%20Yesterday%20evening%2C%20everything%20was%20fine%20with%20Excel.%20This%20morning%20there%20were%20a%20number%20of%20problems.%20I%20also%20thought%20it%20might%20be%20linked%20to%20a%20problem%20with%20the%20%22beta%22%20channel%20and%20went%20back%20to%20%22standard%22%20but%20that%20did%20not%20help.%3CBR%20%2F%3E%3CBR%20%2F%3ENB%3A%20I%20have%20received%20a%20message%20from%20someone%20else%20telling%20me%2C%20MS%20may%20be%20having%20difficulties%20with%20my%20account%20and%20that%20I%20should%20log%20out%20and%20back%20in%20to%20see%20if%20that%20helps.%20I%20will%20try%20that%20once%20I%20finish%20my%20workday%20today.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20your%20input.%20I%20do%20think%20the%20problem%20must%20be%20linked%20to%20the%20%22channels%22%20because%20the%20Unique%20and%20Filter%20fonctions%20are%20not%20available%20everywhere%20and%20with%20every%20system.%20Given%20the%20fact%20that%20I%20have%20a%20Mac%2C%20these%20fonctions%20are%20perhaps%20still%20%22in%20testing%22%20on%20this%20platform.%3C%2FLINGO-BODY%3E
New Contributor

Since today mid afternoon (21/02/2020) , all the new dynamic array function such as UNIQUE and FILTER suddenly desappear from my desktop Excel (still available for my Excel On Line). I am a Office 365 user and I didn't see an update coming.

 

Any idea why ?

 

Thank you

10 Replies

@Fabien_M 

Perhaps channel changes, especially if you apply it by registry key. What do you see here?

image.png

 

@Sergei Baklan are those function only for Office Insider or are they now deployed for every Office 365 users ?

@Fabien_M 

Have no access to monthly channel (if I translated correctly) right now, will check tomorrow. Functions shall be available for this channel.

 

In any case, it's better to send a frown from File->Feedback.

@Sergei Baklan Thank you. You've translated right

 

This make me think about a language problem so I switch my Excel to English and they are available in English !! I switched back to french and they are there again.

 

Weird problem

 

Thank you anyways !

@Fabien_M 

The exact same thing happened to me.  This afternoon, 2/21/2020, I was working on my spreadsheet using the new dynamic arrays.  I closed the files to take a short break.  When I reopened the files, the dynamic arrays were gone.  Formulas such as =UNIQUE(  were replaced with =_xlfn.UNIQUE(.  It is like my version of Excel 365 was taken back in time to a period before the functions were available.  I have version 2001 (build 12430.20288).  I noticed on the Office 365 website it mentions the latest version is 2001 (build 12430.20184) and was released on 2/19/2020.  I did not notice an update.  I reinstalled Office 365 but no change.

@steve37777 

Fixed the problem.  I saw an earlier post about changing languages.  I went to Options -> Language and saw that it was set to "Match Microsoft Windows" and I changed it to "English."  And the new functions are back.

@steve37777 

 

one year later (now 21st May 2021) and I suddenly have the same problem.  I have been using "unique" and "filter" in formulas for quite sometime and this morning, I open one workbook and I get the same message as you "(_xlfn.UNIQUE(_xlfn._xlws.FILTER("...

 

I've tried making every change in the book to the language settings (I'm on a Mac working in France but with UK English as my setting) and nothing works now.  I did not change anything on the settings since stopping work last night and rebooting this morning.  I was on the Excel Beta Channel but have gone back down to the "current" channel.  All to no avail. 

 

Anyone have any ideas?

@RvL_richy 

No ideas. If you change no one setting, reboot and channel was changed - looks very strange. Why don't you to shift on previous channel back?

Hey Sergei,

I am not sure to fully understand what you have written.

You write: "No ideas. If you change no one setting, reboot and channel was changed - looks very strange."

Can I interpret that you too have no idea how this could happen? You've understood what I've said and it all sounds very strange to you? You've noted that I did not change anything but after rebooting, Excel stopped working as before?

Do I also understand that you propose I "change channels" - that I go back to the "standard" channel?

If I've understood what you were asking / proposing, yes you are right. Yesterday evening, everything was fine with Excel. This morning there were a number of problems. I also thought it might be linked to a problem with the "beta" channel and went back to "standard" but that did not help.

NB: I have received a message from someone else telling me, MS may be having difficulties with my account and that I should log out and back in to see if that helps. I will try that once I finish my workday today.

Thanks for your input. I do think the problem must be linked to the "channels" because the Unique and Filter fonctions are not available everywhere and with every system. Given the fact that I have a Mac, these fonctions are perhaps still "in testing" on this platform.