how to use unique and make it a filterable column after extraction

Question

Hi all - I'm trying to use Unique to pull a list of numbers, but when I try to sort by another column, it will say "you can't change part of an array". is this possible? For example, "Colors" is pulled from a second tab using "unique" and from this I enter additional columns that I would like to sort. so I add filters, but when I try and sort the "date" column, I get this message.

Thanks

Answer 1

Vivian should have restricted her answer to the paragraph starting with "Because the UNIQUE function....." as it perfectly explains why you see the warning message. What follows is not relevant and the Excel space on Stack Overflow definitely does not offer more expertise than what's found here.

To return to your issue, you simply can't alter dynamic arrays other than be Dynamic Array (DA) functions that can manipulate DA's.

You could use HSTACK to create a new DA of what's in A2:D6 and wrap it in a SORT function, specifying the column index to sort on and the sort order. Selecting the column to sort on and the sort order could be set-up with check boxes and a text that indicates "ASC" or "DESC".

That could look like in the picture below. Note that this is just a very clunky example.

Answer 2

Dear @Sallin, Joshua,

Thank you for posting your question in the Microsoft Q&A forum.

Because the UNIQUE function creates a dynamic array, the results are considered a single “spilled” range. Excel protects spilled ranges from any manual operations, such as sorting or rearranging, because those actions would require changing part of the array output. When you attempt to sort a column that intersects or depends on this spill range, Excel shows the warning “You can’t change part of an array.”

To help with sorting, in this case, I recommend you sort the data in the original source in sheet 2 and then, it will automatically sync the data's sorting or rearranging.

Moreover, if you need more information or additional Excel formulas, I recommend posting a new question on Stack Overflow. That community is very active with Excel experts and developers who can provide deeper technical guidance. You can post under the tags excel, excel-formula, or any other relevant tags.

Here is the Stack Overflow link: https://stackoverflow.com/questions/tagged/excel

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.  

Note: As Microsoft Community moderators, please understand that my role is to provide general guidance support. We are not involved in product design decisions and do not have direct access to the development roadmap.

I hope this information is helpful. Thank you for your patience and understanding.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.