My plant names table has these three fields: Taxon Name, Name Status, Accepted Name, and Synonyms. Here's the table: https://airtable.com/shrHB9evyZmBawj0C
Taxon Name is for all possible plant names. Some of these are "accepted names", meaning they are the names currently in use. But some names are "synonyms", meaning they were formerly used, but are no longer used for that species. The synonym name has been replaced by an accepted name. If a Taxon Name is a synonym, I mark it as a Synonym in the Name Status field and enter the currently accepted name in the Accepted Name field. The Synonyms field is for keeping track of all synonyms that were previously used for any currently accepted name.
I need a formula for the Synonyms field that does the following: 1. Search the Accepted Name field for non-blank entries. 2. Compare the value in these non-blank records to the values in the Taxon Name field. 3. In the Synonym field, when a Taxon Name field value is equal to a value in the Accepted Name field, return the value(s) of Taxon Name in the Synonym field. This will be a list of all synonyms of any accepted names.
I tried using ChatGPT to get a formula, but it is not coming up with the right answer. You can see the result in the table I linked to. Here's the formula it provided:
IF({Accepted Name}, IF(FIND({Taxon Name}, ARRAYJOIN(ARRAYCOMPACT(IF({Taxon Name} != {Accepted Name}, {Taxon Name}, "")), ", " )), {Accepted Name}, ""), "" )
