Clay Icon

Assistance Needed: Formula to Count Duplicate Domain Names in Table

ยท
ยท

Hi, I'm having a bit of trouble generating the formula from this loom in order to count duplicates. https://www.loom.com/share/a39a4569d95244b7886026f3c70812c7 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. https://app.clay.com/workspaces/154918/workbooks/wb_3dz7XjW4G8vh/tables/t_b9g2BUczSBCm/views/gv_ZQfcSAGNEKoG 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.

  • Avatar of Channeled
    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!

  • Avatar of Anas A.
    Anas A.
    ยท
    ยท
  • Avatar of Mark L.
    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!

  • Avatar of Mark L.
    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?

  • Avatar of Alan T.
    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.

  • Avatar of Alan T.
    Alan T.
    ยท
    ยท

    Testing now

  • Avatar of Mark L.
    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!

  • Avatar of Alan T.
    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.

  • Avatar of Alan T.
    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?

  • Avatar of Mark L.
    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?

  • Avatar of Alan T.
    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

  • Avatar of Mark L.
    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.

  • Avatar of Alan T.
    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! ๐Ÿ™Œ

  • Avatar of Alan T.
    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.

  • Avatar of Mark L.
    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).

  • Avatar of Channeled
    Channeled
    APP
    ยท
    ยท

    We haven't heard back from you in a bit, so we're going to go ahead and close things out here - feel free to let us know if you still need something!

  • Avatar of Channeled
    Channeled
    APP
    ยท
    ยท

    Hi Alan T.! This thread was recently closed by our Support team. If you have a moment, please share your feedback:

  • Avatar of Channeled
    Channeled
    APP
    ยท
    ยท

    Thank you so much for sharing your feedback Alan T.!