How do I create a list of unique names when the column I am referencing could have multiple names?

Maxwell L1200

I am trying to create a unique list of names from a list of existing names. In some instances the original list of names has multiple entries from a drop down, *James *Sam "Barry for example. I ran a query: =IF(COUNTIF(DISTINCT(names$2:names), unique@row) < 1, names@row, ""). This gave me the unique entries from my names entry column but when multiple names were selected for an individual cell I got James Jones, Sam Smith, Barry Barrow I am now trying to write a function that systematically checks if the first name has been used, or the second or the third by using embedded if statements;

=IF(LEN(unique@row) - LEN(SUBSTITUTE(unique@row, ",", "")) = 0, unique@row, IF(COUNTIF([New people]$1:[new people people]337, unique@row) > 0, RIGHT(unique@row, LEN(unique@row) - FIND(",", unique@row)), LEFT(uniqe@row, FIND(",", unique@row) - 1)))

unique = the sorted values from names the ones that look like John Smith or John Smith, Jane doe, mark murphy

new people is the sorted of unique just John Smith

I am curious if there is a better way to do this?

Paul Newcome

Are you able to provide a screenshot for context?

jmyzk_cloudsmart_jp

Hi @Maxwell L1200

I used cell formulas for the [Unique Name List] column in the image below to reference the previous row.

[Unique Name List]1 = JOIN(Names:Names, CHAR(10))

[Unique Name List]2 = IFERROR(SUBSTITUTE([Unique Name List]1, [Unique Name]1, ""), "")

Then, I used column formulas for those;

[Find]=FIND(CHAR(10), [Unique Name List]@row)

[Unique Name]=IF(Find@row > 0, LEFT([Unique Name List]@row, Find@row - 1), [Unique Name List]@row)

https://app.smartsheet.com/b/publish?EQBCT=cbe5049771c4440aabd7ddd074a0d6de