Is there a way to filter duplicates from an inline list?

New Glide user and first time poster!
I have managed to create an app (yay) and realize I may have done things the hard and long way. It is a database of people I have come into contact with, meetings, workshops, etc. on various dates. Each meeting, etc has become it’s own line in a spreadsheet as it has different information - except for the contact name and company they work for - and I have related the offices they work in with an inline list on a details page. So currently I have: “Other People in Office” (say 3 people in that office) and in that list is one person listed twice (I have met them twice/they have 2 rows associated to them due to different dates), one listed 3 times, and the other once. Is there a way to filter and have each person associated with that office listed only once?

Once I get more familiar I will probably go back in and recreate the entire app to streamline the way the data is presented…but am so close to seeing this first version of an app I want - but didn’t know if this was possible in my current setup?

2 Likes

Here’s what I think I would do…

  1. Create a multiple relation that is relating each row to itself by the column of the duplicated text
  2. Rollup the results of that relation by counting the number of matches each row (duplicated ones will give you a result greater than 1).
  3. Create an IFTHEN column to display the title if the result of the rollup is equal to 1
  4. Create an inline list that is filtered to only show the results of the IFTHEN that aren’t empty.

You can probably skip step 3 and do the filtering condition in the inline list too.

2 Likes

Thank you shchc!
I did the rollup count and it populated the new column, but the ones that have 2-6 counts, those names are left out of the new IfThen column entirely. And I need them to appear at least once. Any suggestions?

Hmm good point… I hadn’t considered that.

Here’s what I did in another app (totally different approach, sorry):

  1. Create another sheet in your GSheet
  2. Create a column that has a Unique formula that brings in the unique values from the first sheet’s desired column (i.e. ={“Unique Column”; UNIQUE(Sheet1!A2:A)}

Use that new sheet as the basis for your inline list. You can create a multiple relation that links the multiple rows in the first sheet to the single row in the Unique sheet.

I’m sure there’s another way to do this that is similar to my first recommendation, I just can’t think of the logic that is needed off the top of my head.

1 Like

Thank you again. I am sure I didn’t set up the sheet in the most efficient manner as I could have and will at some point! Until then, I will try this! Thanks again!

1 Like

Update: I was able to create a column with the duplicates but still struggling to find a relation that not only keeps the roll counted names along with one of the duplicate names.

Use a Countifs formula to put a count of how many times that particular email has repeated in the rows above the current row.
Formula: =ARRAYFORMULA(IF(A2:A<>"",COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),""))

Here replace A2:A with the column that has duplicate values and insert this in the 2nd row of a new column.

By this you will have a column which has numbers listed which represent how many times the duplicated value is seen before that row in the same sheet.
Then inside Glide use a filter to show only the rows which have this value as 1

Let me know if you need more clarity!

4 Likes

This sounds great!

THANK YOU! I couldn’t make this code work in my spreadsheet, but was able to make shchc’s count work. I am able to filter in glide, but it omits the duplicates entirely. And I need at least one instance of that record. most have a count of 1 but anything more (2-7) I have tried different filters to at least get them to appear once, maximum.

Simply remove the * from the formula and it will work :sunglasses:

See below

Summary

=ARRAYFORMULA(IF(A2:A<>"",COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)),""))

3 Likes

Never noticed that typo, thanks for pointing out! Edited the original post now.

3 Likes

yup! Amazing… I And then i filtered my inline list using this array instead of combining the two. And I got what I needed! have to go back and study why that code worked and how it is different from the other…!

Question for group, since I used an array in the spreadsheet, then I wouldn’t be able to share or sell the app on glide…correct?

You could still share/sell the app on glide while using spreadsheet formulas. When the app is copied, so is the underlying GSheet.

It is encouraged to build what you can in Glide vs GSheet w/ formula. The performance difference is significant. I hope this helps!

This is what I would do if you wanted to keep everything within glide. First I would recommend having a RowID which will give every row a unique value. This will become important in a little bit. Then I would identify a unique value for each contact, whether it’s a name, phone number, email address, or something else that you know will be in each duplicate row, but is still unique only for that specific person. Let’s assume Name for now. Create a self relation that links the contact’s Name column back onto itself. Make sure this is a single relation. It should always find the first matching row for that contact. Next create a Lookup column that returns the Row ID for the first matching row. Finally create an IF THEN column that will check if the RowID for the row matches the RowID returned from the first matching row in the Lookup column. If it’s a match, then that contact row is the first unique row and you can return a value of ‘true’. If it’s not a match, then the row is most likely a duplicate and you can return a value of ‘false’. Now you can filter your list to only show the ‘true’ rows.

All of this is inside Glide and doesn’t require any sheet formulas…but I would consider reworking your app someday to make this more efficient instead of duplicating data.

8 Likes

Great information. That is something I thought I should do …well after i started creating the app… and when I go back to fix it then I’ll definitely use that!

1 Like

Jeff, let me ask you. If I take my current spreadsheet and add Row ID’s, would I have to delete the duplicate rows first? and then add them back in? Lucky there are only about 9 contacts with 2 or more listings.

No you wouldn’t have to change anything or modify any of your current data. What I wrote is assuming that you leave everything as you currently have it. You should be able to follow the directions and get what you want without having to rely on any sheet formulas.

1 Like

Ok. That worked! I am still so new to this and playing with it. What happens now, as with the previous array I was using in google sheets, is that, sometime a person changes office and I want to keep that current info in its inline list in addition to the older office’s inline list, but it seems to only keeps the contact in the first company that was listed.

i.e Peter started in Company 1: Below him is an inline list for that Company 1 and the others in the Company 1.

If I click on the people in that Company 1, it takes me to their details page and Peter is listed in their inline list for that Company 1.

Peter Goes to Company 2: Below him is an inline list for that company 2 and the others in the Company 2.

However with this one, If I click on the people in that Company 2, it takes me to their details page and Peter is NOT listed in their inline list for that current Company 2.

What I am doing is keeping track of every meeting I have personally had with Peter and the company he is was with at the time of that meeting. But would want the most recent company 2 to definitely have him listed in their inline list not necessarily in the older company 1.

Ideally, It seems I need a row ID associated with the name only, no matter what company or how many times I meet them. They are always connected to that name. But it seems like I would have to have names in a separate sheet first? Still researching how to do it all. In a fun deep dive.

Ok, what I would do is then change your relation to a multiple relation. Then instead of a lookup, create a Single Value column that uses the relation to return the Last RowID, which would be the most recent entry. Then change your If Then column to compare the row’s RowID to the Single Value column. This should always make the last matching row for each contact the ‘true’ row instead of the first matching row.

2 Likes

This is superb. I just used this technique to replace some arrayformulas that were using unique/filter, and saved myself about 500 rows in the process. Love it!

2 Likes