I’ve been asked to find a way to duplicate the CountUnique() function from Gsheets into a form in AppSheet… and it’s proving to be more difficult than I was hoping.
Consider this example:
I’ve got a form with 10 questions, each a number entry where people can enter any number they wish. At the end of the form, I need a count of each unique number entered by users; if they enter 10 unique numbers, I need a number result of 10, if they enter 4 unique, but the rest are duplicates - I need a number result of 4.
When you try and think about a formula to make this happen, it leads me to roads like this:
list(
[Column1],
if([Column2] = [Column1], “”, [Column2]),
if(in([Column3], list([Column1], [Column2])), “”, [Column3]),
etc… you see where this is going, for each column I have to create this conditional statement that looks at everything that has come before; this way each column only adds itself to the list if it’s value is not already present.
This could easily become a monster of a formula.
And this doesn’t take into consideration that now my list will have a blank (which I can remove), but in this example the column is a number - so I’m not sure if the blank would work, it might require a number in which case I’d have to come up with some Place_Holder number and hope no one enters that number.
Unless I’m overthinking things (happens a lot ), I can’t think of a more efficient way of accomplishing this - unless there was a way to reduce the list to unique values.
As always, thanks for considering!!
(And sorry if this is a duplicate, I tried looking but couldn’t find anything about this; only stuff about uniqueID)
PPS: Upon further thinking, the CountUnique could be removed from this request as I could just wrap a COUNT() around the unique() of my list.