Troubleshooting AI for Unique Conditional Formulas in Excel

Hi, I'm having trouble using the AI to help with conditional formulas - maybe I'm not doing something - I have a table of people with their organisations. Some of the people work at the same company, so I'm trying to enrich the company, but I don't want to enrich the same company 10 times. I've done the "only run if.. I've used domain, company to just run if it's unique, if it's different, I've tried inserting a column with the formula: mark yes for the first instance of the company (or domain), and mark no if it's the same company (or domain), and the output always comes out wrong, or the formula is invalid. I don't want to get rid of duplicate companies as they are attached to a person, I just don't want to enrich it, and ideally just be able to copy that info down into those cells

6 comments

Channeled
APP
·
·
We can use AI formulas to mark the first instance of each company without removing duplicates. Here's how: 1. Add a new "formula" column to your table. 2. Use the AI to generate a formula that checks if the current row is the first instance of a company. You can prompt the AI with something like: "Mark 'Yes' for the first occurrence of each unique company, and 'No' for subsequent occurrences." 3. Once you have this formula, you can use it in a conditional run for your company enrichment. This way, you'll only enrich the first instance of each company. 4. To copy the enriched info to all instances of the same company, you can use the "Lookup Multiple Rows" integration. This allows you to scan a column for matching company names or domains and pull in the enriched data for all instances. If you need further assistance with implementing this solution, please request human assistance by clicking on "Talk to a person".
Source: AI Formulas + Conditional Runs [Clay 101] - Clay University
Maria M.
·
·
That's what i did and i'm getting the wrong output or invalid formula if I change the wording slightly
Maria M.
·
·
https://app.clay.com/workspaces/582973/workbooks/wb_0svp19s3btRhCmBUD5U/tables/t_0svp19semPmbrxkzR65/views/gv_0svp19scWQTbV7qBN5V
Bo (.
·
·
Hey, Here’s what to do: 1. Follow this Loom guide: https://www.loom.com/share/a39a4569d95244b7886026f3c70812c7 Use this formula: {{Unique ID}}?({{Lookup Multiple Rows in Other Table}}?.records?.findIndex(r=> r['Unique ID']?.toString()==={{Unique ID}}?.toString())> -1?{{Lookup Multiple Rows in Other Table}}?.records?.findIndex(r=> r['Unique ID']?.toString()==={{Unique ID}}?.toString())+1:2):2 2. After that, run your enrichment only on the rows where the value is 1 (first instance of the company or domain). 3. Then, use a lookup column to pull those enrichment results into the rest of the rows with matching companies. That way, each company is only enriched once, and the data is shared across all related people. Let me know if you’d like help setting that up.
Charles T.
·
·
Wouldn't it be easier to write relevant columns to a new table. In the new table dedupe against a unique identifier i.e. company name, domain name, etc... Run enrichments in the new table and then write back to the original table?
Channeled
APP
·
·
This thread was picked up by our in-app web widget and will no longer sync to Slack. If you are the original poster, you can continue this conversation by logging into https://app.clay.com and clicking "Support" in the sidebar. If you're not the original poster and require help from support, please post in 02 Support.