How to Filter Duplicate Emails in a Column Without Deleting the Whole Row | Clay

How to Filter Duplicate Emails in a Column Without Deleting the Whole Row

Hey team, Is it possible to filter out emails that are already present in a column without deleting the whole row (dedupe)?

39 comments

Augustin R.
·
·
https://app.clay.com/workspaces/48925/tables/t_kQ3dqT6wM8sE/views/gv_XEfPLGKPzzzE
Clay Team
APP
·
·
There is kind of a work-around for this. 1. Use "lookup record in other table" enrichment 2. Set the table to search as the table you want do check for duplicates in 3. Use the email column as both the target column and the row value.Then it will tell you if that email exists anywhere else in the column, and then you can filter based on the "exact match count"
Clay Team
APP
·
·
I just set this up in your table to show you :)
:sparkles-clay:1
Augustin R.
·
·
Thanks Stefan K. , I appreciate it! And how can I delete the email address that is repeated?
Clay Team
APP
·
·
That would be using the dedupe function or just clicking the cell, highlighting the text and deleting the email
Angelo D.
·
·
Can I also use this for my table?
Angelo D.
·
·
https://app.clay.com/workspaces/214609/tables/t_betvq8My6Ypw/views/gv_jZNvSHsOV1pA
Angelo D.
·
·
I have like duplicate domains
Clay Team
APP
·
·
Yes Angelo that would also work for you with domains.
:sparkles-clay:1
Angelo D.
·
·
Ok trying it our rn
:arc:1
:sparkles-clay:1
Angelo D.
·
·
So indeed it says, 2 matches found when it is duplicate. How can I delete one of them?
Stefan K.
·
·
you can use the dedupe feature inside of the column header
Stefan K.
·
·
For example:
Angelo D.
·
·
Didnt know about that future, many thanks 🙏
:arc:1
Stefan K.
·
·
My pleasure 😄
Augustin R.
·
·
Hey Stefan K. I don't understand how to delete just the email, not the whole row.
Clay Team
APP
·
·
Hey Augustin R., jumping in here too. Is there a reason why you'd like to keep a duplicate row? You can delete the email within the cell by overwriting it but if you want to have unique records in your table, then deduping it is best.
Augustin R.
·
·
Hey Arturo O., yes so I want to contact Hotel Managers or hotel owner. Sometimes two hotels have the owner but not the same hotel manager. So I want to keep the row because they are two different hotels. If I can delete the repeating hotel, I can then add a different email instead.
Clay Team
APP
·
·
Got it, makes sense! I made this video today, showing how to prevent the same person from getting enriched twice, but not sure if it fits your workflow. Just in case: https://www.loom.com/share/a39a4569d95244b7886026f3c70812c7?sid=3a542e61-558d-44fe-8d70-2b408d315e12
Clay Team
APP
·
·
And bunch more videos in this loom folder, about troubleshooting or similar workarounds to the above: https://loom.com/share/folder/d65215fa3205492fbf61d25a61285bd9
Augustin R.
·
·
Thanks I'll explore this!
Augustin R.
·
·
Thanks for the explanation! I think I did something wrong because it's not returning 2 when the lookup function shows 2. :(
Clay Team
APP
·
·
Sorry for the delay, Augustin! checking
Clay Team
APP
·
·
Looks like you did this correctly but that's the first instance for that person so they should be number one. I just caught the edge case though, where cell size data could be an issue. Will have to think about that one a bit more cause looks like there's just too much information for some people
Clay Team
APP
·
·
We can make it default to a 2 to prevent them from getting enriched altogether only when that message appears. Let me know if that would be okay with you and I can update the prompt so it does it
Clay Team
APP
·
·
To show you that you did this right, here's an example of a person being a duplicate.
d3675d7a-a1aa-45d7-b77e-be31d8b5fd94.png(41 kB)
Augustin R.
·
·
I'm not sure I understood you very well:/
Clay Team
APP
·
·
No problem, Augustin. Regarding setting up the same logic as in the video, you did that correctly.
Clay Team
APP
·
·
Besides that, you have a few people that have too many duplicates in the table. Since you have a lot of columns and then the lookup runs, it's running into "too much information" issues and it cannot tell you how many matches it found. Does that make sense? My question is related to that, if when it returns too much information, do you want the last formula to return a different number other than 1?
Augustin R.
·
·
Ah I see! I would like it to return 2 so that I could rule it out in the run settings. Otherwise this would not help me in my goal. I hope it makes sense
Clay Team
APP
·
·
Okay, will adjust the formula so it does that then
Clay Team
APP
·
·
Just before I add anything, were these columns removed or which table was it? I can add them back in if needed
Augustin R.
·
·
Yes I removed them, sorry 😕
Augustin R.
·
·
Hey, data from my table disappeared 😕 I don't know what happened but I added a filter, "only show rows with sebastien.bazin@accor.com" and when I deleted the filter, it deleted the data. Could you look into it please?
Clay Team
APP
·
·
Yep, sorry, had a few calls but checking
Clay Team
APP
·
·
How many rows did you have? I see you still have a filter applied but says there's over 2k rows
e3f97305-aabe-4771-8ea3-fdfe2bec26db.png(6 kB)
Augustin R.
·
·
Yes so I'll figure out something don't worry!
Siddhant B.
·
·
Note that you won't be able to distinguish rows having those emails / data points i.e., if we have 2 rows containing augustin@romaneschi.com and we apply this logic, both will have the row count as 2. We need to use an external table or data store and then take an action the first time we encounter a unique email, and take another action when we run into the same email. Does that make sense?
Clay Team
APP
·
·
@Siddhant Bhansali. Yep, that does make sense! The method above would label the first record as 1 if it appears in the first position of records found in the lookup, and if cell size limits are reached all new rows should default to 2 and prevent enrichments from running. The caveat is when a bulk import has multiple of the same value including the first time an email appears in the table and runs into that issue, size limits, the first record could also be labeled as 2 as you said.