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!
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.
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!
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
Never noticed that typo, thanks for pointing out! Edited the original post now.
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.
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!
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.
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.
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!
That did work. Thanks Jeff. My only glitch now is when I am looking at their details card and see the office list below, that persons picture is there too. Not that it’s a big deal, which I am beginning to not mind, but how cold I omit that person from appearing in the office list when I am looking at their details card: to maintain the “Other People in this office” list without seeing them too?
That sounds amazing. I am glad my problem brought Jeff in with that solution for you too!
You should be able to filter the list using Screen Values where the name is not the name of the current contact being viewed.
Thank you Jeff. That makes so much sense. I truly enjoy learning these bits that allow me to re-evaluate my problem solving skills!