Can I find unique values and put them in a column?


#1

I have a column of Job Names and would like to indicate which Job Names are unique in that column. Has anyone successfully done this?

Essentially I want to do the opposite of checking for duplicates and returning duplicate values in a new column.


#2

Maybe you could convert that field (column) into a Linked Record type, so then you can count the times that has been linked with a Count field, and then order and filter by that field.


#3

Here is a base that does what you want.

Your de-duplication comment is quite pertinent, as this functionality and de-duplication share a common profile: They both wish to take vertical data (for instance, a column of field values) and manipulate them horizontally — here, comparing one against the others. Accordingly, the first step is to transform vertical data into horizontal data by gathering that column’s worth of values into a single row.

To do so, begin by creating a new table called [Calc]. Initially, [Calc] should contain only a single record, and that record should contain only a single field, {Name}. In {Calc::Name} for Row 1, type a period character (’.’).

Next, define a new single line text field in [Job Names] called {Link to Calc}; set the value of all {Link to Calc} fields — that is, set the value of {Link to Calc} for every record in the table — to the period character (’.’). Once every {Link to Calc} field is set to ‘.’, right-click on the field header, select ‘Customize field type,’ and change the field type from ‘Single line text’ to ‘Link to another record.’ When prompted, select [Calc] as the table to which to link. Disable ‘Allow linking to multiple records’ and click ‘Save.’

Airtable converts all those ‘.’ values to links to the [Calc] record named ‘.’. Go to the [Calc] table and define a new rollup field that uses the reciprocal link to [Job Names] to roll up the {Job Name} field using the aggregation function ARRAYJOIN(values,'|'). (Specifying the vertical bar character as the function’s separation character ensures base formulas will work with a job name that contains a comma character (’,’).)

Finally, in the [Job Name] table, define a new field to indicate unique job names — that is, job names only found once in the column of values. The demo base includes two possible implementations, one that uses emojis to indicate which names are (‘:white_check_mark:’) or are not (‘:x:’) unique, the other displaying only unique names. Both are rollup fields that compare the length of the complete list of job names with the length of the list after all instances of the current job name have been removed: If the two lengths differ by the length of the current job name, the name is unique.

As usual, dummy data courtesy Fake Name Generator.


#4

@W_Vann_Hall - You’re literally a legend :sparkles:


#5

Hi @W_Vann_Hall - this was great but could you please provide the rollup formula you used for the last step to indicate if a value was unique or not? I have been toying with it for a while and can’t quite suss it out.

Thank You!


#6

You can copy the base to check it.


#7

Thanks! It didn’t occur to me to copy the base…