COUNT duplicate value

AppSheet AppSheet Q&A
,
1

I want to count the number of duplicate entries on value submit.

Say I have two values in the datasource, [ID] and [No]. On submitting a new entry to [ID], I want [No] to equal the count the number of times that [ID] has been submitted previously.

2

Here’s a basic approach. Depending on the specific context of where you’re producing this tally, there may be more efficient techniques.

COUNT(SELECT(Table[ID], AND([_ROWNUMBER] < [_THISROW].[_ROWNUMBER], [ID] = [_THISROW].[ID])))
1 Like
3

I’m hoping to produce the “tally” into the [No] (short of Number) column on saving. So when you click save, the formula, set in the [No] column will calculate the number of previous time the [ID] has been saved. Hope that makes sense.

4

Did you enter the expression I drafted in the App formula property of the [No] column? Did it accomplish what you’re seeking?

1 Like
5

It does sort of work yes.

6

I say it sort of works, what I mean is it only applies to anything newly added. It doesn’t look back at all previous entries to the sheet. So for instance, an [ID] of “1234” has 1 previous entry. If I add a new entry to the sheet it calculates the [No] as 1 whereas it should be 2 because there is a previous entry.

7

By “the number of times that [ID] has been submitted previously”, I interpreted that you only wanted to count previous entries, excluding the current new entry.

Try:

COUNT(SELECT(Table[ID], AND([_ROWNUMBER] <= [_THISROW].[_ROWNUMBER], [ID] = [_THISROW].[ID])))
2 Likes
8

It does work perfectly. That is great thank you.

1 Like