UNIQUE Function instability

%3CLINGO-SUB%20id%3D%22lingo-sub-2060924%22%20slang%3D%22en-US%22%3EUNIQUE%20Function%20instability%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2060924%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20Excel%20365%2C%20I%20built%20a%20spreadsheet%20that%20included%20the%20UNIQUE%20function%20for%20the%20first%20time%20last%20night.%20It%20saved%20with%20no%20problem%2C%20but%20when%20i%20opened%20it%20this%20morning%2C%20the%20spreadsheet%20would%20only%20hang%20with%20a%20Calculation%20Error%20that%20never%20got%20past%200%25.%20I%20closed%20down%20and%20rebooted%2C%20and%20the%20spreadsheet%20then%20opened%20with%20the%20%22Repaired%22%20tag.%20Upon%20investigation%2C%20I%20found%20the%20UNIQUE%20function%2C%20which%20formerly%20had%20the%20format%20%22%3DUNIQUE(S9%3AS1342)%22%20had%20been%20replaced%20with%20an%20array%20formula%20%22%7B%3DUNIQUE(S9%3AS1342)%7D%22.%20The%20formula%20had%20been%20pasted%20into%20every%20cell%20that%20had%20a%20value%20(as%20opposed%20to%20the%20formula%20being%20in%20the%20top%20cell%20only%20and%20filling%20in%20cells%20below%20with%20values).%20Finally%2C%20all%20the%20the%20formatting%20in%20the%20spreadsheet%20had%20been%20reset%20to%20defaults%2C%20and%20all%20of%20the%20charts%20in%20the%20spreadsheet%20were%20gone%20-%20I%20assume%20part%20of%20the%20%22Repair%22%20process.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHas%20anyone%20else%20had%20problems%20with%20stability%20of%20this%20function%3F%20Any%20fixes%3F%20Thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2060924%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2061176%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20Function%20instability%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2061176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F929102%22%20target%3D%22_blank%22%3E%40Frank_Knapp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20a%20bug%2C%20hope%20one-time%20one.%20Never%20seen%20that%20in%20my%20practice.%20In%20any%20case%20I'd%20recommend%20to%20Send%20a%20Frown%20form%20within%20Excel%2C%20Microsoft%20monitors%20them.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DUNIQUE(S9%3AS1342)%20converted%20to%20the%20set%20of%20array%20formulas%20you%20may%20reproduce%20manually%20-%20enter%20the%20formula%20in%20any%20cell%20with%20Ctrl%2BShift%2BEnter%2C%20it%20returns%20only%20one%20value.%20Drag%20it%20down%2C%20formula%20returns%20one%20by%20one%20values.%20Perhaps%20that%20Excel%20done%20on%20Repair%20and%20that's%20not%20specific%20of%20UNIQUE%20formula%2C%20just%20dynamic%20array%20calculations%20were%20transformed.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Using Excel 365, I built a spreadsheet that included the UNIQUE function for the first time last night. It saved with no problem, but when i opened it this morning, the spreadsheet would only hang with a Calculation Error that never got past 0%. I closed down and rebooted, and the spreadsheet then opened with the "Repaired" tag. Upon investigation, I found the UNIQUE function, which formerly had the format "=UNIQUE(S9:S1342)" had been replaced with an array formula "{=UNIQUE(S9:S1342)}". The formula had been pasted into every cell that had a value (as opposed to the formula being in the top cell only and filling in cells below with values). Finally, all the the formatting in the spreadsheet had been reset to defaults, and all of the charts in the spreadsheet were gone - I assume part of the "Repair" process.

 

Has anyone else had problems with stability of this function? Any fixes? Thanks!

1 Reply

@Frank_Knapp 

It looks like a bug, hope one-time one. Never seen that in my practice. In any case I'd recommend to Send a Frown form within Excel, Microsoft monitors them. 

=UNIQUE(S9:S1342) converted to the set of array formulas you may reproduce manually - enter the formula in any cell with Ctrl+Shift+Enter, it returns only one value. Drag it down, formula returns one by one values. Perhaps that Excel done on Repair and that's not specific of UNIQUE formula, just dynamic array calculations were transformed.