+ Reply to Thread
Results 1 to 3 of 3

UNIQUE FILTER formula automatically getting converted into an Array formula in O365

  1. #1
    Registered User
    Join Date
    02-24-2023
    Location
    India
    MS-Off Ver
    365
    Posts
    9

    UNIQUE FILTER formula automatically getting converted into an Array formula in O365

    In the attached Excel, I have a formula (tab SUMMARY, E6) which is a UNIQUE, FILTER one.

    =IF(C5=0,"No Feedback Available",(IF(B3="Weekly Summary",(UNIQUE(FILTER('WEEKLY DETAILS'!G3:G9999,'WEEKLY DETAILS'!D3:D9999<>""))),(UNIQUE(FILTER('MONTHLY DETAILS'!G3:G9999,'MONTHLY DETAILS'!D3:D9999<>""))))))

    I have uploaded the Excel on AWS so it can be used by a program that writes value in the 1st and 2nd tab of the Excel.
    When I am downloading the report which is generated after the program writes data into it, the formula in the cell E6 is appearing with curly braces in the beginning and at the end, like the following:

    {=IF(C5=0,"No Feedback Available",(IF(B3="Weekly Summary",(UNIQUE(FILTER('WEEKLY DETAILS'!G3:G9999,'WEEKLY DETAILS'!D3:D9999<>""))),(UNIQUE(FILTER('MONTHLY DETAILS'!G3:G9999,'MONTHLY DETAILS'!D3:D9999<>""))))))}

    Due to Array formula, only the first unique value is appearing in the result, an

    Any ideas on how can I prevent my formula from being automatically converted into an Array formula? Thank you for your guidance in advance.

    There is a thread for the same issue on Microsoft Answers but us locked and unresolved: answers.microsoft.com/en-us/msoffice/forum/all/automatic-change-of-regular-formulas-to-array/4fb2d834-7f7c-4b21-bb43-690fb5ba244c
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: UNIQUE FILTER formula automatically getting converted into an Array formula in O365

    Any ideas on how can I prevent my formula from being automatically converted into an Array formula
    Put an @ in front of the formula (after the =)

  3. #3
    Registered User
    Join Date
    02-24-2023
    Location
    India
    MS-Off Ver
    365
    Posts
    9

    Re: UNIQUE FILTER formula automatically getting converted into an Array formula in O365

    Hi Pepe,

    Applying 'At' symbol does not seem to work. I am getting only the first value after adding the symbol. Please see the file attached
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] UNIQUE FILTER formula showing only first unique value and not all
    By adyrockstar in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-24-2023, 07:45 AM
  2. [SOLVED] Formula not returning next unique value in array.
    By chriswrcg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2022, 11:11 AM
  3. Replies: 10
    Last Post: 10-26-2020, 12:18 PM
  4. #Formula small unique without array #
    By daboho in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-22-2016, 05:49 AM
  5. Replies: 10
    Last Post: 12-16-2015, 03:16 PM
  6. Replies: 6
    Last Post: 05-26-2012, 04:56 AM
  7. array formula - getting unique list?
    By neowok in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-05-2005, 09:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1