Counting instances of text within text (finding duplicates)?
Hi,
We’re battling getting contact duplicates from Meta lead campaigns. Basically people forget they’ve already signed up, and send in a new registration. Since these are Meta, we cannot control anything upstream.
A first step is to get our people, who only work in interfaces, alerted that there’s a duplicate to a contact. It would therefore be enough, for now, to achieve a field that tells the user the amount that the current user’s e-mail address can be found in the total base (~2.000 contacts now).
I’m trying an approach where I link all contacts to one record in another table, then lookup all e-mail addresses. Back in my main table I link to the second table and lookup the e-mail addresses.
I would the like to count the instances of the field “email” in the lookup field where all e-mail addresses are. FIND finds only the first and gives the position, SEARCH does the same. COUNT and COUNTALL do not work on text.
How could I get the count?
Rgds.
Björn
Page 1 / 1
How are these contacts originally getting into your system?
Since your users only have interface access, they don’t yet have access to Airtable’s DeDupe extension, which allows you to easily cleanup duplicates that are already in the system. But you have access to that extension, so you can do a one-time cleanup.
If you want to do a DeDupe cleanup that doesn’t involve extensions, I demonstrate one cool technique on this Airtable podcast episode.
However, your best approach would probably be to prevent the duplicates from ever getting into your system to begin with.
If you’re receiving the contacts via a CSV file, then I would recommend using Make’s CSV automations to automatically merge the duplicates upon import.
This was exactly what I was looking for. Didn’t expect it to come in the shape of LEN-formulas, but I have an intuitive understanding of the logic there.
Thanks again!
Björn
Thanks @ScottWorld for you reply. The problem is we get the contacts in from Meta lead forms, so there’s no way to filter them there. Maybe Make’s FB integration could be useful, but for right now what I can deal with was the LEN-formula thing suggested by @TheTimeSavingCo.
@0800-grizzly
Yes, that’s what I was referring to above — as soon as a Meta lead form is submitted, Make can check to see if that record already exists in your Airtable base before adding the new lead into Airtable.
It can also merge a duplicate lead as well.
I demonstrate this process in my video that I linked to above (although that demonstration is done from the context of a CSV file).