CountUnique() and Unique() formulas

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 :laughing:), I can’t think of a more efficient way of accomplishing this - unless there was a way to reduce the list to unique values. :wink:

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. :slight_smile:

This?

COUNT(
  LIST(
    [Column1],
    [Column2],
    [Column3],
    ...
  )
  - LIST()
)

Any list subtraction has the side-effect of removing duplicates.

1 Like

You would think right!? lol

But it seems that sometimes a 3 isn’t the same as another 3 when done this way.

HUH?!?!

testing. I tried this before and ended up with duplicate items in the list.

This prompted me to look for an actual formula (like unique()) but found it’s not supported by appsheet, which led me to this post. :slight_smile: lol

I’m trying it again now to see if things work.

1 Like

I agree that a UNIQUE() function would be nice.

1 Like

Epic. :fireworks: I just love it when things that didn’t work before, now do. :stuck_out_tongue:

So I ran a test and sure enough it pulls out the unique values.

Thanks Steve!!!

1 Like

Hmmm… seems we have mixed results.

When testing, it works; when in practice it doesn’t. I’ve come accross a few formulas like this, where they work in the editor but in the app they don’t; Praveen talked about it’s because the two systems are actually that - two different systems (one the app editor, the other the app).

Made a quick video going over things:

CloudApp

Hmm… who to tag…

@praveen

1 Like

Is there a reason why you can’t do the formula in Google Sheets and just have the user sync the App to get the result? Or have a workflow email it automatically?

1 Like

Not sure if this is what you’re after:
Matt

1 Like

Thanks for the formula suggestion Simon, but no there’s two options here:

  1. I go with the list subtraction method proposed by Steve (which works, its just not evaluated correctly by the app at the moment so I’m stuck waiting for a bug fix); or
  2. I go with the brute force method where each successive column checks to see that it’s value is not already present in the previous ones.

And for why I can’t have this in the google sheet… well any spreadsheet formula is like sticking gum in the gears of your app. It might work when you’ve got 4 records, maybe even when you’ve got 40 - but once you get to 400 or 4,000 things start to really slow down.

If you’re building an app for longevity, then when possible I would advise anyone to migrate any spreadsheet formula into appsheet.

1 Like

I encountered the situation where a 3 isn’t the same as another 3. lol

Here’s the setup:

  • I’ve got 17 columns, each with a formula controlling the value (so there’s always a number there, with the default being 0)

I setup a formula where each column is contained inside it’s own LIST() expression and I’m adding all the columns together; this should have the result of discarding duplicates because if I have a 2 in the list already and I add another one, it’s already there.

But this isn’t working, it seems that the 2 from column1 is different from the 2 in column4.

Crazy. :crazy_face:

— here’s the formula:
count(
(
list([hvlt01.1s]) + list([hvlt01.2s]) + list([hvlt01.3s]) + list([hvlt01.4s]) + list([hvlt01.5s]) + list([hvlt01.6s]) + list([hvlt01.7s]) + list([hvlt01.8s]) + list([hvlt01.9s]) + list([hvlt01.10s]) + list([hvlt01.11s]) + list([hvlt01.12s]) + list([hvlt01.13s]) + list([hvlt01.14s]) + list([hvlt01.15s]) + list([hvlt01.16s]) + list([hvlt01.17s])
)
- list(0, 99)
)

I should note that the list subtraction is working, but not the list addition (at least in the sense of discarding duplicates… but it IS adding each element to the list, so I GUESS it’s technically correct). :slight_smile:

1 Like

I’m not clear why option 2 is appropriate. Why do you need to check the status of past but not future answers?

1 Like

If I always have the answers looking backwards, then I can confidently know that any future answer won’t be a duplicate while also knowing I will always get one answer in the list.
CloudApp

Here’s the formula translation:

list(
[Column1],
if([Column2] = [Column1], “”, [Column2]),
if(in([Column3], list([Column1], [Column2])), “”, [Column3]),
if(in([Column4], list([Column1], [Column2], [Column3])), “”, [Column4]),
etc....) - list("")

So for future answers are you removing the previous duplicates from the list of allowable choices?

1 Like

Indeed good sir. :slight_smile: If you review the image, it shows a spreadsheet visulization of the principle:
CloudApp
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.)))

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.

4 Likes

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