How to show duplicates values?

Hello Gliders,

I am currently making an app that would help recruit teams into a football tournament team. I want to ask how I can use Glide to check for some conditions as follows:

  • The teams will have 7-12 players and each will be identified with a Name/ID.
  • The data of the team will be pulled from a Google sheet, and the data of each player will be pulled from columns ;

Email /Team Name / Name 1 / ID 1 / Name 2 / ID2 / Name 3 / ID 3 /…

  • I am using the array formula to check for duplicates with COUNTIF and if the results return a value > 0, Glide will disable some of the functions of the app for the user.

I like to ask how I can display the duplicate Name/ ID values to the users that will display which Name/ ID is duplicate. Currently, I can only put a hint that says “Found ID Duplicates” and the user will have to find who is the duplicate by themselves.

Much help would be appreciated.

When you say duplicates, I assume that you mean duplicate names within a single team, yes?

Let me first say that this would be much easier to deal with if you restructured your data a bit.
What I would do is just use two columns for each team. One for the team name, and then a second column being a joined list of player IDs. With this approach you could use a multi-select choice component to assign players to teams, and it would be impossible to have duplicates in the first place.

That said, given your existing structure, probably the simplest approach I can think of would be as follows:

  • Create a template column that concatenates all the player IDs, and returns a comma separated list.
  • Pass that list into a JavaScript column that processes the list and returns the IDs of any that are duplicated.

Thank you, Darren.

  • The duplicates can also happen in multiple teams, for example, Team Z, can also have a player from Team A or B.
  • The players’ data is collected through Jotform and then passed to Google Sheets. So I cannot make a choice component to assign players to the teams.

As for right now, there are about 70 teams with a total players around 700 players. I am not sure that it will make Glide unresponsive but I will try to implement what you have suggested.

Okay, if you need to check across all teams, then you will need a joined list column to gather the lists for all rows, and then process that.

Question: Do you have a separate table that lists all of the PlayerIDs?

If you do, or can create one, then here is a simpler approach that doesn’t require JavaScript:

  • Create a Make Array column in your Teams table that generates an array of PlayerID’s from your 12 PlayerID columns.
  • In your Users table, create a multiple relation column that matches the UserID’s with the array of UserIDs in your Teams table.
  • Now add a Rollup column that takes a count via the multiple relation.
  • Any Users that return a count greater than 1 are duplicated, and if you want to know which teams they are duplicated in, you can use a Joined List column via the same multi-relation to get a list of team names.
1 Like

Following your instructions, I’m not sure if I missed something or might have done something wrong. Here’s a screenshot.


Here is the table structure in the app.
Form Responses : Email / Team Array
Users : Email / lookup Team Array

Relations | Users: Team Array with Form Responses: Team Array (Match Multiples)

It seems there’s nothing to let the rollup function count.

Which table is your first screenshot from? I can’t tell.

Do you mean you have a lookup column in your Users table? That isn’t what I suggested. Please take another look at my instructions and follow them carefully. I tested the solution with some dummy data before posting, so I know it will work if setup correctly.