Posted 7 March 2024, 8:02 am EST - Updated 7 March 2024, 8:08 am EST
I am trying to get a list and count of unique values in a column.
This doesn’t appear to be working the same as in Excel.
What is the correct usage for this? Screenshot included.
UNIQUE function doesn't seem to be working
Posted by: brian.ploe on 7 March 2024, 8:02 am EST
-
brian.ploe
-
brian.ploe
Posted 7 March 2024, 8:34 am EST - Updated 7 March 2024, 8:39 am EST
This gets weirder. The docs say this function is supported as of 13.0 yet it is not in the list of autocomplete functions.
I am running version 17.0.1
-
brian.ploe
Posted 7 March 2024, 9:56 am EST
Ok, I discovered that I need to enable Dynamic Arrays for this to be available. I turned it on and verified it works but now I’m running into an issue where using the UNIQUE function in a data validation list formula causes the workbook to become corrupted.
I tried to enter =UNIQUE(‘Tab1’!$C$5:$C$20) to use for a data validation list and everything broke horribly, and I had to recover from an older saved version. I repeated this test a few times with the same results.
The formula =‘Tab1’!$C$5:$C$20 works but I need unique values for this list.
Any help would be appreciated. Thanks!
-
chandan.rawat
Posted 8 March 2024, 3:11 am EST - Updated 8 March 2024, 3:16 am EST
Hi,
As per my understanding, you are setting data list validation to the cell range C5:C20 and then using this cell range inside the UNIQUE() function. However, using cell reference $C$5:$C$20 in the UNIQUE() function causes an issue.
I tried to replicate the issue at my end by setting list validation in cell range C5:C20 and then using UNIQUE(Sheet1!$C$5:$C$20) in the sheet. However, I did not face any issues. I tested at SpreadJS Designer at https://developer.mescius.com/spreadjs/designer/index.html. Kindly refer to the attached GIF.
Please explain the issue in more detail. Also, provide us with a sample that replicates the issue and the .xlsx file in which you are facing the issue. So that we can thoroughly investigate the issue at our end and find the root cause of the issue.
Regards
