CountUnique() and Unique() formulas

[MultiTech]

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.

[MultiTech]

Indeed good sir. If you review the image, it shows a spreadsheet visulization of the principle:

each column is looking at the one’s behind it, if that column see’s that it’s value is already present in the list, it excludes itself.

The formula breakdown essentially says the following:

1. Add the first column, we always want it’s value as a starting point
2. Is the second column’s value the same as the first’s? If it is, leave a blank entry, otherwise add the second column’s value to the list.
3. Is the value of the third column in either the first or second columns? If so, leave it blank, otherwise add the value to the list
4. Is the value of the fourth column in any of the previous columns (1, 2, or 3)? If so, leave it blank, otherwise add the value to the list.
5. etc… continuing on for each column to be included this way (17 in my case)
6. Then finally, at the end, we remove blanks from the list. (Instead of using blanks you could elect to use a placeholder then remove the placeholder - this works nicely.)

So by establishing the pattern of the leading column looking at the preceding columns (to check if it’s value has already been used), I can guarantee that I won’t have duplicates.

But I have to brute force this, creating a series of cascading IF() statements that’s looking at previous columns.

---

The ability to feed a list to a UNIQUE() function would neatly solve this; it would look something like this:

count(unique(list([Column1], [Column2], [Column3], [Column4], etc.)))

[pravse]

My mind hurts trying to understand this!

But my takeaways: (a) we need a UNIQUE() function for lists, and (b) we need to ensure that LIST difference removes duplicates in the app — I checked and this is a bug with a trivial fix. So will roll that out after the weekend. Thanks for pointing this out.

In the absence of the UNIQUE() function, I suspect (List - {}) might achieve the same outcome once we deploy the bug fix.

[Aleksi]

And @praveen… this is the same we discussed/played with Adam yesterday.

[yoshi]

In any cases, unique function is good as a syntax sugar.

[Steve]

help.appsheet.com

UNIQUE()

Unique items in list

[MultiTech]

COUNT(UNIQUE([List]))

[Roderick]

Good stuff, but this is not currently planned. We'll keep you posted if that changes!