Assistance Needed: Formula to Count Duplicate Domain Names in Table

Alan T. · 2024-10-22T07:40:03.051Z

Hi, I'm having a bit of trouble generating the formula from this loom in order to count duplicates. Could someone please assist? The AI formula generation is not working for me. If you can please help correct my syntax the goal is to count and assign count number domain names on this table. I was planning on doing the same thing for prospect LinkedIn profiles in order to prevent dupes and also put a cap on the number of prospects engaged per company.

Assistance Needed: Formula to Count Duplicate Domain Names in Table | Clay

Channeled
APP
·
·
💤 You've caught us outside of our support hours (9am-9pm EST), but don't worry - we'll be back in touch within 24 hours (often sooner!). If you haven't already, please include the URL of your table in the thread below so that we can help you as quickly as possible!
Anas A.
·
·
Alan T.
Mark L.
·
·
Hi Alan, thanks for sharing that. I'll have a look and see if we can get this formula set up like the video has it. I'm not yet sure what's going awry, but I'll report back!
Mark L.
·
·
I've added a new formula column called "ClayTest – AccountUID Occurence". The formula in place on that one is: {{AccountUID}} ? {{Lookup Multiple Rows in Other Table}}?.records?.map(record => record['Unique ID']).indexOf({{AccountUID}}) + 1 : 2 I spot-checked a couple of the duplicates (metro.coop, bankdora.com, wvista.com), and I think it gets to the goal you had in mind. How's it look to you? If all good, could you try adopting that approach for your LinkedIn profiles, and let me know how it goes?
Alan T.
·
·
Thanks for your help Mark! If I'm understanding your formula syntax correctly, it iterates through the records found and counts them 1 to infinity, right? If so, that was my intended goal and works for my use case.
Alan T.
·
·
Testing now
Mark L.
·
·
Right, in layman's terms, you might summarize it as: "If there's an AccountUID, look through the table's records, and find the index of the record with a matching Unique ID. If you find a match, return the index plus one. Otherwise, if there's no AccountUID, just return the number 2." Keep me posted!
Alan T.
·
·
Hey Mark, that's a bit different from what I was going for actually. The goal was to count the number of appearances and assign position in the count. That way I can apply it to domains and count the number of prospects per email domain and restrict to less than 4 for example.
Alan T.
·
·
Does this formula logic still apply? Also, I noticed the 'Unique ID' string is bracketed in the formula. If I'm calling the records 'AccountUID' or 'LeadUID' would I need to adjust that string accordingly?
Mark L.
·
·
I think we're on the same page! In each set of records, each item has a different index/position. So the formula finds the position, and increments it for user-friendly display (e.g. 1 instead of 0). As for the 'Unique ID' in my formula: I used that because it matches the parameter in the array. https://downloads.intercomcdn.com/i/o/w28k1kwz/1225072911/65cdb4c2991118d447d3a1aa93a6/uinique-id-in-record.png?expires=1729622700&signature=c69901bfbf17a17a75b8988a0289924c18a7d6f3a676300a801225d2eb560c6b&req=dSIlE8l5n4heWPMW1HO4zRvSdabtdvhrEfjMiGk%2FkBuXnOEcslGD3cJq4t1f%0AQsOU%0A I suspect this is an artifact of running the column, and re-naming it after the fact. You'll just need to follow the same principle: check the cells in the Lookup column, and make sure your formula uses the same name. Does that help?
Alan T.
·
·
Thanks Mark, that makes sense-- and I think you're right, it probably is an artifact from running it under that name earlier, I did rename it after the first run IIRC
Mark L.
·
·
You're welcome! And I appreciate your patience with me – these things are hard to talk through sometimes. :) I'll keep this thread on-hand for the moment. If any other hiccups or oddities, please let me know.
Alan T.
·
·
Thank you-- I'm poking around as we speak to test it, but things are starting to make sense for me now. Really appreciate your help! 🙌
Alan T.
·
·
Mark, would you please take a look at the table again? Having a bit of trouble with implementing on the column titled "Account Row Position" under the lead table. https://app.clay.com/workspaces/154918/workbooks/wb_3dz7XjW4G8vh/tables/t_KeCWgogogAba/views/gv_ewvsaXfb82x3 I'm expecting it to count to 4 on this view, but can't get it to iterate through the list correctly. Probably a syntax issue, but I can't figure out exactly what I'm doing wrong.
Mark L.
·
·
Looking at the table for people... https://app.clay.com/workspaces/154918/workbooks/wb_3dz7XjW4G8vh/tables/t_KeCWgogogAba/views/gv_ewvsaXfb82x3 I think the root issue here is that the AccountUID column is not unique. It looks like it's pulling that value from the company table. Because each of the account rows has the same ID, the formula just returns the first match – the first index/position. I'd recommend updating the AccountUID formula to generate a random number (as you did in the company table).

17 comments